Power BI filter - Latest forecast version

[jayjay0306]

Hi people,
I am in a bit of a puzzle here and hope you guys can help:
I have the following table showing our forecast version:

Our forecast process works in the following way:
On the first day, each month, we get a new "SOP Version" and "LAG 1". After some days the "latest" LAG-version changes from "LAG 1" to "LAG 0" for the rest of the month.

Example in table: Latest "SOP Version"=SOP5. From "day 1" to fx. "day 4" the table will be loaded with "LAG 1" only. On "day 5" the table will be enriched with "LAG 0".

Now, what I want to make is a dynamic filter, which always filters the latest "SOP" and "LAG"-version on the Power BI report I have.

I have managed to make a summarized table in Power BI filtering the "lowest" LAG value for each SOP-version:

SOP&LAG (Latest Vers) = SUMMARIZE('Forecast';'Forecast'[SOP Version];"LAG (lowest value)";MINX('Forecast';'Forecast'[LAG]))

But I can't figure out to isolate the the latest SOP Version (SOP5) with its latest LAG-version (LAG0).

I am not even sure this is the right approach?

All ideas will be greatly appreciated.
Thanks.
Br,
Jakob

[v-eachen-msft · Accepted solution]

Hi @jayjay0306 ,

You could share your expected result here with image or table.

And you could try the following DAX:

Table 2 =
SUMMARIZE (
    'Forecast',
    'Forecast'[SOP Version],
    "LAG (lowest value)", CALCULATE (
        SELECTEDVALUE ( Forecast[LAG] ),
        FILTER (
            Forecast,
            RIGHT ( Forecast[LAG], 1 ) = MINX ( Forecast, RIGHT ( Forecast[LAG], 1 ) )
        )
    )
)

[jayjay0306]

thanks v-eachen-msft, it got me the rest of the way 🙂

Br,

JayJay0306

[amitchandak]

@jayjay0306 , can we create a desc Rank on version and use Rank =1 to filter

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...