Forum Discussion
Helper IIIHow to add previous row value in current row value while having 2 categorical variables ?
Solution SageHello Birinder,
I'd solve your task with the following calculated column:
Here's the [DAX] code in plain text:
PrevSum =
VAR CurrentKey = [KEY]
VAR PrevYear = YEAR ( [Date] ) - 1
VAR PrevRRT = MINX ( FILTER ( ALL ( data ), AND ( [KEY] = CurrentKey, YEAR ( [Date] ) = PrevYear ) ), [RRT] )
RETURN IF ( ISBLANK ( PrevRRT ), 0, [RRT] + PrevRRT )Best Regards,
Alexander
BirinderHi barritown,
Thanks for the reply.
The code works almost perfectly, however there is still one issue.
The code will work when we have continous series of years i,e 2020,2021,2022 and so on.
There are some cases where there are year skips such as 2020,2022,2023,2026.
In those cases the results are not coming up right and they are zero.
Please ignore the AMT column.Is there a way only for PrevYear varible , via which we can choose earlier period than the current one.
Like in screenshot, for second last row:
12/31/2045 date should refer to its very previous date i.e. 12/31/2043, which is picking 12/31/2044 as of current solution and giving the values as zero.
Do you know a way by which we can achieve this.
Many thanks in Advance.- barritown
Hi Birinder,
Sure, please try this CC:
And the same in plain text:
PrevSum v2 = VAR CurrentKey = [KEY] VAR CurrentDate = [Date] VAR PrevYear = CALCULATE ( YEAR ( MAX ( data[Date] ) ), ALL ( data ), data[KEY] = CurrentKey, data[Date] < CurrentDate ) VAR PrevRRT = MINX ( FILTER ( ALL ( data ), AND ( [KEY] = CurrentKey, YEAR ( [Date] ) = PrevYear ) ), [RRT] ) RETURN IF ( ISBLANK ( PrevRRT ), 0, [RRT] + PrevRRT )Best Regards,
Alexander