Forum Discussion

tkramer's avatar
tkramer
Icon for Advocate I rankAdvocate I
3 years ago

Dynamic Dax measure

Hey everybody.  I've got a DAX question.  

I have a Sales Order table that has been snapshotted periodically the past couple years.  We have just started to snapshot this every weekday (Mon-Fri).  What I've done is gotten the "Average Total Open Value per Snapshot" and used that as my first column in this matrix to calculate the Average Open Value in that given month.  Here is that measure: 

 

AVG Total Open Value per Snapshot = AVERAGEX( VALUES('DIM-Date'[Date]), [Sum of Total Open Value]) --(I am using my dimension date table, rather than my snapshot table here)

 

For the second column, I want to calculate the difference in the "Average Total Open Value per Snapshot" and the "Average Total Open Value per Snapshot Last Month" (using DATEADD, 1, MONTH).  Here is a screenshot of the matrix below at the Year-Month level and the measures used to calculate "LM Diff":

 

Total Open Value LM = IF( ISBLANK([AVG Total Open Value per Snapshot] ), BLANK(), CALCULATE( [AVG Total Open Value per Snapshot], DATEADD( 'DIM-Date'[Date], -1, MONTH ) ))

 

Total Open Value Diff LM = IF( ISBLANK([Total Open Value LM] ), BLANK(), [AVG Total Open Value per Snapshot] - [Total Open Value LM] )

 

 

However, now that we are snapshotting every weekday (Mon-Fri), I want to calculate the difference from the most recent Snapshot Date and the Previous Snapshot Date.  Here is a screenshot when I expand down to the day level: 

 

 

Basically, I want that "LM Diff" column to by dynamic and work even when I expand down to the day level.  Instead of being the difference from Last Month, it would be the difference from last snapshot.  Currently, it is just going exactly 1 Month back and capturing that value.  Help would be greatly appreciated!

2 Replies

  • Read about ISINSCOPE  and adjust your measure accordingly.

  • Hi,

    So at the day level, you want to take the difference from the previous week and at the month level, difference from the previous month.  If my understanding is correct, share the download link of the PBI file.