Archive for the ‘DAX’ Category

DAX – Dynamic Time Calculations

April 6, 2013 Leave a comment

It is very common to have many measures in OLAP system for trend analysis like Current, MTD, YTD besides previous year comparisons. Here discussing a trick to get all date calculations for all measures dynamically without creating any calculations at measures.

Step 1: Query PowerPivot model from Excel to get all Measures with a dummy number into Excel, this is so dynamic to get all measures from power pivot model rather than hardcoding but off course we can also create a table with target measure as
Measures from model

Step 2: Link the table back to Model as
Measures link to Model

Step 3: Create another table in Excel with required Period Calculations as
Time Calculation Table

Step 4: Link Excel table back to Model as
Period Table link to Model

Step 5: Here is the trick, Create a Calculated Measure to understand the selected measure and it can be used as an intermediate measure for later use
Get Selected Measure

Step 6: Calculated Measure from Step 5, Used in the Measure Calculations hence the time calculations are independent of measure and it can be passed from step 5 selections
Dynamic Time Calculations

Step 7: Created a sample report with Measures table in slicers and Measure Calculations into values from Period table and Periods into Columns, the selected slicer value can be captured at Step 5 and then passed to Step 6 calculations to get the results
Here selected Sales Amount and Order Quantity for testing
Measure Sales from slicer
Selection from Slicer

Points to consider:
1. This trick is very useful if we have many measures for trend analysis
2. It is important to understand DAX context in step 5 and step 6
3. It can be used with only one measure at a time unlike MOLAP dynamic date calculations

Categories: DAX Tags: