DAX – Dynamic Time Calculations

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


Step 2: Link the table back to Model as


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


Step 4: Link Excel table back to Model as


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


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


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



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