Forum Discussion
JJ_masgio
3 years agoRegular Visitor
Calculate expression by previous row
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...
- Anonymous3 years ago
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
Anonymous
3 years agoNot applicable
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