Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
rainbow_test
Frequent Visitor

Last measure result that meets condition


I am trying to create a table with a measure that displays actuals + forecast headcount. For actuals I need the measure to display available data, but for future months I want to retrieve the latest available month actuals and add cumulative movements (which I have on a separate measure).

My problem is getting to replicate that last actuals headcount total. I've got the below but it returns actuals for actuals months, and cumulative movements only for forecasted months. What am i doing wrong?

 

Headcount =

VAR maxDate =

    CALCULATE (

        MAX ( Periods[Period] ),

        FILTER ( 'Forecast', 'Forecast'[Forecast vs Actual] = "Actuals" ))

RETURN

    IF (

        FIRSTNONBLANK('Forecast'[Forecast vs Actual],"Actuals") = "Actuals",

        [FTEs-Forecast-sum],

        CALCULATE (

            SUM('Forecast'[FTEs-Forecast]),

            Periods[Period] = maxDate,

            ALL(Periods[Period]),

            ALL('Forecast'[Forecast vs Actual])

        )
        + [FTEs-cumulative_mov]

    )

rainbow_test_0-1689956400572.png

 

1 REPLY 1
foodd
Super User
Super User

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).


https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...


Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

 

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.