Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi!
Quite new to DAX.
This is my fact table:
Date (dd-mm-yyyy) | Value |
03-06-2023 | 987 |
23-06-2023 | 1112 |
02-07-2023 | 1234 |
12-07-2023 | 1543 |
I'm interested in the delta Value across months... but the measure I'm interested in is sush as:
Delta[07] = 1543 - 1112
I mean is the difference between the MAX Value of month 07 and the MAX Value of month 06.
Of course I have a complete Calendar table.
Can you help me in filtering?
Thanks a lot!
Solved! Go to Solution.
Hi @JJ_masgio ,
I created a sample pbix file(see the attachment), please check if that is what you want.
Measure =
VAR _selyear =
SELECTEDVALUE ( 'Calendar'[Date].[Year] )
VAR _selmonth =
SELECTEDVALUE ( 'Calendar'[Date].[MonthNo] )
VAR _curmvalue =
CALCULATE (
MAX ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
YEAR ( 'Table'[Date] ) = _selyear
&& MONTH ( 'Table'[Date] ) = _selmonth
)
)
VAR _premdate =
EOMONTH ( DATE ( _selyear, _selmonth, 1 ), -1 )
VAR _premvalue =
CALCULATE (
MAX ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
YEAR ( 'Table'[Date] ) = YEAR ( _premdate )
&& MONTH ( 'Table'[Date] ) = MONTH ( _premdate )
)
)
RETURN
IF (
ISBLANK ( _premvalue ) || ISBLANK ( _curmvalue ),
BLANK (),
_curmvalue - _premvalue
)
Delta =
IF (
ISINSCOPE ( 'Calendar'[Date].[Year] ),
[Measure],
SUMX (
GROUPBY ( 'Calendar', 'Calendar'[Date].[Year], 'Calendar'[Date].[Month] ),
[Measure]
)
)
Best Regards
Hi @JJ_masgio ,
I created a sample pbix file(see the attachment), please check if that is what you want.
Measure =
VAR _selyear =
SELECTEDVALUE ( 'Calendar'[Date].[Year] )
VAR _selmonth =
SELECTEDVALUE ( 'Calendar'[Date].[MonthNo] )
VAR _curmvalue =
CALCULATE (
MAX ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
YEAR ( 'Table'[Date] ) = _selyear
&& MONTH ( 'Table'[Date] ) = _selmonth
)
)
VAR _premdate =
EOMONTH ( DATE ( _selyear, _selmonth, 1 ), -1 )
VAR _premvalue =
CALCULATE (
MAX ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
YEAR ( 'Table'[Date] ) = YEAR ( _premdate )
&& MONTH ( 'Table'[Date] ) = MONTH ( _premdate )
)
)
RETURN
IF (
ISBLANK ( _premvalue ) || ISBLANK ( _curmvalue ),
BLANK (),
_curmvalue - _premvalue
)
Delta =
IF (
ISINSCOPE ( 'Calendar'[Date].[Year] ),
[Measure],
SUMX (
GROUPBY ( 'Calendar', 'Calendar'[Date].[Year], 'Calendar'[Date].[Month] ),
[Measure]
)
)
Best Regards
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |