Get previous value of a table and show as card

[PowerBI_Explore]

Hi there

I've got a table with 3 columns "Company", "Month" and "Rating":

Now I'd like to get a new column (or measure) to get the "Rating" of the previous available month.
If there isn't a value for an earlier month respectively it's the first entry (like Company A / Month 1), the value 0 should be displayed.

My final goal is to have two slicers, one for "Company" and one for "Month" and two card visuals with the information about the "current" rating and the previous rating:

How can I solve this by using DAX?

Thank you in advance for your help!

Best regards,

Tom

[jaipal · Accepted solution]

@PowerBI_Explore Create a Index Column starts with 1

and create column with this DAX:

Previous Rating =

CALCULATE(MAX('Table'[Rating]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1))

Here is your expected result:

[PowerBI_Explore]

Works perfectly like expected, appreciate your help - thank you very much!

Only need to figure out, how I should handle the first entry / record of every company correctly, so that it will display no value instead of the last value of the previous company. I think I should be able to solve this with the same logic and an additional check, if the company is the same then the one with index-1.

Best regards

Dominique