How to add previous row value in current row value while having 2 categorical variables ?

[Birinder]

Hi Community,
I have a dataset table named as "IBNS".
Under it, I have 3 columns as follow:-
1. KEY (Data type - text)
2. Date (Data type - Short Date)
3. RRT - (Data type - Decimals)
 
I want to add the values of second row into the first one, and I want that for each category.
For example: Add the 12/31/2024 date data to 12/31/2023 date for one category and same like that for othe category as well.

You can get the idea of what I want from the Screenshot attached.

I have tried the code below, But it is not working.

PrevSUM = CALCULATE(SUM(IBNS[RRT]),IBNS[KEY]=EARLIER([KEY]),DATESINPERIOD(IBNS[Date],SELECTEDVALUE(IBNS[Date]),2,DAY))

Can anyone help me here.
Many thanks in Advance.

[Ahmedx · Accepted solution]

pls try this

Sample PBIX file attached

https://1drv.ms/u/s!AiUZ0Ws7G26RiDCBBmeuF22LQvUS?e=UxGIAa

[barritown]

Hello @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

My YouTube vlog in English

My YouTube vlog in Russian

[Birinder]

Hi @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 · Accepted solution]

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

My YouTube vlog in English

My YouTube vlog in Russian

[Birinder]

Thanks @barritown 
It is working now.
Thanks a lot for this.
Also ,Thanks to @Ahmedx for the soluton.
Peace.

[Ahmedx · Accepted solution]

pls try this

Sample PBIX file attached

https://1drv.ms/u/s!AiUZ0Ws7G26RiDCBBmeuF22LQvUS?e=UxGIAa