Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to Solution.
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,
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,
@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! |
Thanks for the response, but doesn't work if I choose non contiguous dates, for example 11/30/2020 and 12/31/2019
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |