Getting the previous record values

[saidareddy_madi]

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.

[Anonymous · Accepted solution]

Hi @saidareddy_madi ,

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.                                     

[saidareddy_madi]

I have joined the new table with existing table, now it's filteing the previous values aswel.  it's worked fine.. thanks for your help. 

[saidareddy_madi]

Thanks for your help, my requiremet got littlebit complicated.. these values will change based the year filter selected by the ender user in the slicer. 

[ryedoesbi]

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 🙂

[ChielFaber]

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.