Skip to main content
cancel
Showing results for 
Search instead 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

Reply
rbustamante
Helper I
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:

rbustamante_0-1610178038714.png

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
 
Please, anyone can help me
 
 

 

 

 

 

1 ACCEPTED SOLUTION
Payeras_BI
Super User
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

 

Payeras_BI_1-1610379040619.png

 

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

View solution in original post

3 REPLIES 3
Payeras_BI
Super User
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

 

Payeras_BI_1-1610379040619.png

 

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
CNENFRNL
Community Champion
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

Screenshot 2021-01-09 110438.png


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!

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.