Forum Discussion

jayjay0306's avatar
jayjay0306
Icon for Helper III rankHelper III
6 years ago
Solved

Power BI filter - Latest forecast version

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

Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    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 ) )
        )
    )
)

     

3 Replies