LASTNONBLANK value in last 12 months

[apanta]

Hi,

I am currently stuck with one problem in Power BI. What I am looking for is, in the below table, I want to find the latest score for the patients in the last 12 months based on the slicer selection.

For example, If I select any month after September 2023 to September 2024, the score should show me the result as 2 since the latest Score is 2 on 2/9/2023 but if I select the month as October 2024, the score should come up as zero since there was no data in the last 12 months.

Similarly, when the slicer is selected as July 2023, the result should come up as 3 because the latest score is 2 which was created on 1/6/2023.

Hope I am clear on this.

Thank you!

[amitchandak · Accepted solution]

@apanta , Create a date table, join it with your date, and use that in visual, slicer, and measure

Rolling 12 = CALCULATE(Lastnonblankvalue( Table[Created Date], Max(Table[Core])) , DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

[apanta]

Thank you so much, Amit! It worked like a wonder.

Can I please ask you one more question? In the below table, I want to give a score of 0 to the "Chemical" type. For example, if any month is selected after September 2023 to September 2024 (e.g. October 2023), I want the formula to check which is the latest date and give me a score of 0 as the type is "chemical". The formula you mentioned is working fine, but if I filter out the chemical type then it is capturing the score as 3 from 1/6/2023 which I don't want.

is there an any way to get this result?

Regards