previous day
1 Topic
Calculate value of the previous available day for each data item (multiple items per date)
Hi everyone, I have been looking for a solution that would look up the table by the name of an account and create a column with their values for the previous available reporting date (T-1). This would enable to track the change between every T-day and T-1 day. Report day Account T Value T-1 Value 10.05.2024 ABC 100 0 10.05.2024 DEF 300 0 10.05.2024 XYZ 250 0 14.05.2024 ABC 50 100 14.05.2024 DEF 380 300 14.05.2024 XYZ 210 250 15.05.2024 ABC 150 50 15.05.2024 DEF 420 380 15.05.2024 XYZ 200 210 16.05.2024 ABC 170 150 16.05.2024 DEF 400 420 16.05.2024 XYZ 230 200 I tried to use the following measure but it will be always looking into the second to last day inputted regardless of the slicer selection: T-1 Value = VAR MaxDate = CALCULATE(MAX('Table'[Report Day]), ALL('Table'[Report Day])) VAR PreviousDate = CALCULATE(MAX('Table'[Report Day]), 'Table'[Report Day] < MaxDate) RETURN CALCULATE( SUM('Table'[Value]), 'Table'[Report Day] = PreviousDate ) Would you have any suggestions for a formula that would look into the previous available date for every data line? If a new account appears the T-1 should naturally be 0. Thank you!Solved2KViews0likes7Comments