Forum Discussion
Getting the previous record values
| StoreName | FormDate | Sum of TotalScore |
| A | 09-11-2022 00:00 | 2 |
| A | 05-05-2023 00:00 | 95 |
| A | 16-11-2023 00:00 | 99 |
| A | 23-04-2024 00:00 | 98 |
| A | 19-11-2024 00:00 | 96 |
| B | 02-11-2022 00:00 | 2 |
| B | 12-04-2023 00:00 | 94 |
| B | 08-11-2023 00:00 | 95 |
| B | 09-04-2024 00:00 | 90 |
| B | 13-11-2024 00:00 | 94 |
| C | 15-11-2022 00:00 | 1 |
| C | 12-04-2023 00:00 | 92 |
| C | 15-11-2023 00:00 | 99 |
| C | 17-04-2024 00:00 | 89 |
| C | 21-11-2024 00:00 | 92 |
| D | 15-11-2022 00:00 | 1 |
| D | 12-04-2023 00:00 | 94 |
| D | 15-11-2023 00:00 | 98 |
| D | 17-04-2024 00:00 | 96 |
| D | 21-11-2024 00:00 | 98 |
I have the in the above format and need to display the data in the table visual like the below format.
Store Name Latest Score Previous Score
A 96 98
B 94 90
C 92 89
D 98 96
Any ideas how to achive this in Power BI. Any inputs must be approciated. Thanks for advance.
Hi Anonymous ,
I create a table as you mentioned.
Then I think you can create a new table and here is the DAX code.
NewTable = SUMMARIZE ( 'Table', 'Table'[StoreName], "Latest Score", CALCULATE ( MAX ( 'Table'[Sum of TotalScore] ), 'Table'[FormDate] = MAX ( 'Table'[FormDate] ) ), "Previous Score", CALCULATE ( MAX ( 'Table'[Sum of TotalScore] ), 'Table'[FormDate] = MAXX ( FILTER ( 'Table', 'Table'[StoreName] = EARLIER ( 'Table'[StoreName] ) && 'Table'[FormDate] < MAX ( 'Table'[FormDate] ) ), 'Table'[FormDate] ) ) )Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
The easiest way would probably to use the previous function. Check out visual calculations and the previous function
Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn
A superb overview by Jeroen ter Heerdt:
Visual Calculations In Power BI
Hope this helps.You could use the RANKX function (descending order) where 1 = your latest date and 2 = your next latest date. You could use the TOPN function to retrieve the top two values each category (i.e. latest date and second latest date) and use the MINX function to get the lowest of these two values. You could use the INDEX function to retrieve the 2nd value from your list when ordered by descending date values.
There's lots of options with DAX 🙂
Hi Anonymous ,
I create a table as you mentioned.
Then I think you can create a new table and here is the DAX code.
NewTable = SUMMARIZE ( 'Table', 'Table'[StoreName], "Latest Score", CALCULATE ( MAX ( 'Table'[Sum of TotalScore] ), 'Table'[FormDate] = MAX ( 'Table'[FormDate] ) ), "Previous Score", CALCULATE ( MAX ( 'Table'[Sum of TotalScore] ), 'Table'[FormDate] = MAXX ( FILTER ( 'Table', 'Table'[StoreName] = EARLIER ( 'Table'[StoreName] ) && 'Table'[FormDate] < MAX ( 'Table'[FormDate] ) ), 'Table'[FormDate] ) ) )Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
