topic Power BI: Get the Last/Latest Value of a Category/Group in Quick Measures Gallery

Power BI: Get the Last/Latest Value of a Category/Group

amitchandak — Sun, 10 Jul 2022 05:00:34 GMT

Power BI: Get the Last/Latest Value of a Category/Group

Problem Statement:

In the given data filter and show only the most recent row based on date/version etc.

Data: I have taken data where we have ID and date along with status. And we would like to show the latest status.

Solution:

We will use a DAX measure for that.

I have loaded the data to the power bi file and created the following DAX measure

Last Status = var _max = maxx(filter(ALLSELECTED(Data), Data[ID] = Max(Data[ID])), Data[Date]) return CALCULATE(max(Data[Status]), filter((Data) , Data[Date] =_max))

Refer to the attached file.

Re: Power BI: Get the Last/Latest Value of a Category/Group

Anonymous — Mon, 09 Oct 2023 19:16:27 GMT

Hi amitchandak this is fantastic. I am using this to flag the latest version of an ID (in my case application reference) and it works great in a table visual. However, there is one issue which I was hoping you could help with - I have a 'stage' column which I use to slice the table visual. When I slice using the stage column it doesn't show the latest IDs in that stage, but instead shows all records for the selected stage. e.g. out of all the latest applications 10 are in stage x, but when I put stage x in a slicer it shows 16 (it basically shows all instances of x historically). I know the issue is around filters, but I can't seem to get it to work.

Re: Power BI: Get the Last/Latest Value of a Category/Group

NewUser1989 — Sat, 14 Oct 2023 05:39:43 GMT

Hi, amitchandak.

I want to display using the card visual the recent value from my table (an excerpt from the original table), the recent % diff, which in this case is 0.29. And will remain so and will not be affected by the slicer until there is another value inputted under "Submitted" that will give another value for %diff. I can't seem to make this happen with lastdate and max function. Is this possible?

Thanks and best regards.

Date	Submitted	% from total	% running total	Current % total	Previous % total	% diff
Oct 07	6	0.44%	47.8070%	47.81	47.37	0.65%
Oct 08				47.81	47.81	0.00
Oct 09				47.81	47.81	0.00
Oct 10				47.81	47.81	0.00
Oct 11				47.81	47.81	0.00
Oct 12				47.81	47.81	0.00
Oct 13	9	0.66%	48.4649%	48.46	47.81	0.66
Oct 14	4	0.29%	48.7573%	48.76	48.46	0.29