Home > DAX > DAX – Dynamic Time Calculations

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
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

Advertisements
Categories: DAX Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: