1 Reply Latest reply on Nov 6, 2013 1:42 PM by Mark Smith

    Display two semi-independent measures on common date dimension

    Caleb Smith

      I have two measures I want to display on a common date dimension. One measure is easy: I just want to show the distinct count of records by initial approval date (see attached workbook). After that , for each fiscal year and quarter, I also want to show the total number of "active" records. "Active" is defined by a somewhat complex calculation and I can't figure out how to relate both measures back to a common date dimension.

       

      I'm having trouble describing what I'm trying to do, sorry. Essentially I have two measures that don't naturally share a common date dimension, so I want to figure out a way to relate them both to an artificial date dimension so I can display them on a dual axis. I'm attaching a screenshot of what I want the graph to look like, along with 10k rows of dummy data.

       

      Please let me know if I can provide any additional information. I've kind of hit a wall with this and don't know how I can tackle it.

        • 1. Re: Display two semi-independent measures on common date dimension
          Mark Smith

          Caleb,

           

          To get calendar date & fiscal date to properly translate, you have a few options.  In the Data windows on the left you can right click on the dimension that is a fiscal date and pick Default Properties > Fiscal Date Start > [Start Month].  This will give you a kinda one size fits all translation.  Often, people use a date table with a Calendar column and a Fiscal column, with a row for each day that needs to be translated.