Last non blank month with context issues

[lemaribdb]

Hi!
I have issue with below measure. It works when there are no additional columns that would "break down" the Actual/Plan/Forecast measure. But as soon as there is such column together in the matrix with below, the values are wrong.

I've noticed it's because the NO(ISBLANK()) gets evaluated separately for every "category" it's broken down into, instead on the whole measure. Below is the representation what the measure does. .

Year	Period	Job Category	Raw Amount	LastNonBlankPeriod
2023	11	Finance	5	11
2023	11	Sales	10	12
2023	12	Finance	BLANK()	11
2023	12	Sales	11	12

Because Finance is BLANK in 2023, it takes period 11 into account for this specific category. Where my goal is it should always be 12 in below case as there are some values for period 12.
And my values from below measure, if I remove period split will be: Finance 5, Sales 11. Instead of Finance 0, Sales 11

How can I fix the measure?

Headcount Switch Yearly = 
SUMX(
    VALUES('X_Calendar Period'[F_YEAR]),
    VAR CurrentYear = 'X_Calendar Period'[F_YEAR]
    VAR LastActualsPeriod = 
        CALCULATE(
            MAX('X_Calendar Period'[F_YEAR_MONTH_NO]),
            FILTER(
                ALL('X_Calendar Period'),
                'X_Calendar Period'[F_YEAR] = CurrentYear &&
                NOT(ISBLANK([Headcount By Heads]))
            )
        )
    VAR LastForecastPeriod = 
        CALCULATE(
            MAX('X_Calendar Period'[F_YEAR_MONTH_NO]),
            FILTER(
                ALL('X_Calendar Period'),
                'X_Calendar Period'[F_YEAR] = CurrentYear &&
                NOT(ISBLANK([FCST Headcount By Heads]))
            )
        )
    VAR LastPlanPeriod = 
        CALCULATE(
            MAX('X_Calendar Period'[F_YEAR_MONTH_NO]),
            FILTER(
                ALL('X_Calendar Period'),
                'X_Calendar Period'[F_YEAR] = CurrentYear &&
                NOT(ISBLANK([PLAN Headcount By Heads]))
            )
        )
    RETURN
    SWITCH(
        TRUE(),
        CurrentYear <= [Actuals Year], CALCULATE([Headcount By Heads], 'X_Calendar Period'[F_YEAR_MONTH_NO] = LastActualsPeriod),
        CurrentYear = [Forecast Year], CALCULATE([FCST Headcount By Heads], 'X_Calendar Period'[F_YEAR_MONTH_NO] = LastForecastPeriod),
        CurrentYear = [Plan Year], CALCULATE([PLAN Headcount By Heads], 'X_Calendar Period'[F_YEAR_MONTH_NO] = LastPlanPeriod),
        BLANK()
    )
)

[some_bih]

Hi @lemaribdb I do not have enought information from your side about model and relatioship so it is hard to spot issue/s.

Still, just based on your measure provided, I would focus on part

CurrentYear = 'X_Calendar Period'[F_YEAR] and try CurrentYear = SELECTEDVALUE('X_Calendar Period'[F_YEAR])

Another area to check soomething is your measures, not listed in your post, like [FCST Headcount By Heads] etc.

But, back to your issue if I understand correctly: you only want to figure out what is the latest available data for your  different "planned" periods, like last forecast period. If yes, and in your model there is Calendar / Date table connected to other "planned" periods with relationships, then your request should be simple find max of that "planned" periods data?

[Anonymous]

Hi @lemaribdb ,

It's not quite clear what your arithmetic logic is, but you could try changing not isblank to <>blank(). Since isblank returns a boolean value, and you're using &&, it will probably be judged as “both true”.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.