cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## How to calculate measure based on previous date

Hi all.

In excel there is a feature to calculate or show the values in a Pivot Table based on the difference of the previous record. I show the option:

I'm trying to do this in DAX but it doesn't run ok. I have a table with the amounts of products at a given date and the product name. I made the following measure:

mDifference =
VAR _ThisMonth =
CALCULATE ( SUM ( 'Products (2)'[Amount] ) )
VAR _PreviousMonth =
CALCULATE ( SUM ( 'Products (2)'[Amount] ), PREVIOUSMONTH ( 'Date'[Date] ) )
RETURN
_ThisMonth - _PreviousMonth

Always return 0.

In the model I've defined a Date Table
Here is the pbix file

1 ACCEPTED SOLUTION
Super User

Hi @rbustamante ,

For non contiguous months try this one:

``````mDifference =
VAR _CurrentDate =
SELECTEDVALUE ( 'Products (2)'[Date])
VAR _PreviousDate =
CALCULATE (
MAX ( 'Products (2)'[Date] ),
ALLSELECTED ( 'Products (2)'[Date] ),
KEEPFILTERS ( 'Products (2)'[Date] < _CurrentDate )
)
VAR _ThisMonth =
CALCULATE ( SUM ( 'Products (2)'[Amount] ) )
VAR _PreviousMonth =
CALCULATE (
SUM ( 'Products (2)'[Amount] ),
'Products (2)'[Date] = _PreviousDate
)
RETURN
_ThisMonth - _PreviousMonth``````

Regards,

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain
3 REPLIES 3
Super User

Hi @rbustamante ,

For non contiguous months try this one:

``````mDifference =
VAR _CurrentDate =
SELECTEDVALUE ( 'Products (2)'[Date])
VAR _PreviousDate =
CALCULATE (
MAX ( 'Products (2)'[Date] ),
ALLSELECTED ( 'Products (2)'[Date] ),
KEEPFILTERS ( 'Products (2)'[Date] < _CurrentDate )
)
VAR _ThisMonth =
CALCULATE ( SUM ( 'Products (2)'[Amount] ) )
VAR _PreviousMonth =
CALCULATE (
SUM ( 'Products (2)'[Amount] ),
'Products (2)'[Date] = _PreviousDate
)
RETURN
_ThisMonth - _PreviousMonth``````

Regards,

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain
Community Champion

@rbustamante , you might want to try

``````mDifference =
VAR _ThisMonth = CALCULATE ( SUM ( 'Products (2)'[Amount] ) )
VAR _PreviousMonth = CALCULATE ( SUM ( 'Products (2)'[Amount] ), PREVIOUSMONTH ( 'Products (2)'[Date] ) )
RETURN
_ThisMonth - _PreviousMonth``````

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!
Helper I

Thanks for the response, but doesn't work if I choose  non contiguous dates, for example 11/30/2020 and 12/31/2019