topic Re: How to calculate measure based on previous date in DAX Commands and Tips

How to calculate measure based on previous date

rbustamante — Sat, 09 Jan 2021 07:54:12 GMT

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

https://we.tl/t-xaf4zegkBd

Please, anyone can help me

Re: How to calculate measure based on previous date

CNENFRNL — Sat, 09 Jan 2021 10:05:08 GMT

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

Re: How to calculate measure based on previous date

rbustamante — Sat, 09 Jan 2021 11:24:43 GMT

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

Re: How to calculate measure based on previous date

Payeras_BI — Mon, 11 Jan 2021 15:37:19 GMT

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,