I have a data which is ordered based on Group, Year, Q and looks like this ( I left the Value YTD column unfilled here):
Year Q Group Value YTD
2022 1 A
2022 2 A
2021 4 B
2022 1 B
2022 2 B
2022 2 C
2021 4 D
2022 1 D
2022 2 D
I'm trying to get the YTD values translated to quarterly values based on the groups. Here is what I have tried:
if(Previous(Group) = Group, if(Previous(Year) = Year, "Year YTD" - Peek ("Year YTD"), "Year YTD" ), "Year YTD" ) as "Value quarterly"
However, I get the wrong values and I don't understand why. What caveats my thinking has and is there another way to do this?
