Measure isn't including all dates up to selected date with row filter context

[smallpox]

Hi Everyone, I'd greatly appreciate help with the measure formula as I'm having problems configuring a measure that SUMs the 'People'[Relocation] column between the period of interest. This measure is part of an overall headcount dashboard to track employee numbers and changes.

The measure result for 'HC Change Relocation' is supposed to sum 'People'[Relocation] between the most recent upload date (if no filter selected) OR the date selected to the last day of the fiscal year (3/31).The measure works when I do not filter by my 'Date' dimension table by correctly summing up [Relocation] within the evaluation range using the latest 'People'[date in] dates. (e.g. Today is 1/28/2025. the Max('People'[date in]) value is 12/31/24, the most recent upload date of headcount for 'People'[Worker]. The evaluation period correctly starts from 3/31/2024 (last date of the prior FY) to 12/31/2024). The measure breaks when I do use a slicer for date 'Date'[FY] , 'Date'[FYQ]. Instead, When I select 'FY2024' > nested 'Q3' (the same quarter as the latest 'People'[date in] value of 12/31/24, the measure only evaluates the sum in quarter 3 (10/31/24 to 12/31/24). Not from 3/31/24 to 12/31/24.

Here's some facts about the layout:

• 'People' fact table, where each line lists a distinct worker as of an effective 'People'[date in] when their headcount is uploaded monthly. 'People'[Relocation] integer column exists as this cannot be calculated due to how we classify relocations. Instead, -1 or 1 is entered on each line item to delineated an employee leaving one location effective during a certain [date in] upload.
• 'Company' dimension table has a one to many relationship from 'Company'[Company] to 'People'[Company].
• 'Date' dimension table is an autocalendar table with one to many relationship from 'Date'[date] to 'People'[date in]. 'Date'[FYQ] is fiscal year quarter formatted as Q1, Q2,Q3,etc. 'Date'[FY] formatted as FY2024, etc.

I have a matrix with rows 'Company'[Company], values of different headcount measures for the latest headcount for the prior fiscal year, current fiscal year (based on date selected), the HC change b/w the fiscal year, and 'HC Relocation Change', the sub-category of HC Change classified as 'Relocation'. See images below for context.

Slicer not engaged, current numbers look good. Image to bottom right shows correct values from HC Relocation Change measure variables. The values of 1 and -1 took place during Q2 of FY2024.

Measure is incorrect. The 'People'[Relocation] was only summed within the FY quarter dates from 10/31/24 thru 12/31/24, as opposed to 3/31/24 to 12/31/24 as specified in the Test measures (bottom right)

Here's the formula for HC Relocation Change: 

HC Change Relocation =

VAR CurrentFY = MAX('Date'[FY]) -- Get the current fiscal year from the Date table context

VAR PriorFY = "FY" & (RIGHT(CurrentFY, 4) - 1) -- Calculate the prior fiscal year (e.g., FY2024 -> FY2023)

VAR PriorFYLastMonthDate =

    CALCULATE(

       max('Date'[Date]), -- Start of the last month of the prior fiscal year

        FILTER(all(('Date')), 'Date'[FY] = PriorFY)

    )

VAR EvaluationStartDate = PriorFYLastMonthDate -- Start date for evaluation from the last month of the prior fiscal year

-- Max date logic from People[date in] column, considering slicer context on the 'Date' table

VAR MaxEndDate =

    CALCULATE(

        MAX(People[date in]),

        ALLSELECTED('Date') -- Ensures the slicer context on 'Date' is respected, while not over-filtering People[date in]

    )

RETURN

    CALCULATE(

        SUM(People[Relocation]),

        FILTER(

            People,

            People[date in] >= EvaluationStartDate && People[date in] <= MaxEndDate

        )

    )

Text in orange is what gives me problems, as evaluating each variable (see bottom right) of images has the correct value.

Using ALL in the return function results in this error:

RETURN

    CALCULATE(

        SUM(People[Relocation]),

        FILTER(

            ALL(People),

            People[date in] >= EvaluationStartDate && People[date in] <= MaxEndDate

        )

 

Using the slicer for Q3 and Q2 spits out the same 0, showing that it's including the full evaluation range. Row filter context is not respected.

Greatly appreciate someone's help with this. This has had be stumped for a couple days.

[smallpox · Accepted solution]

Incredible. Sometimes detailing things out suddenly result in one finding out their own solution! In the return portion of the measure, I simply changed it to 

RETURN

    CALCULATE(

        SUM(People[Relocation]),

         FILTER(

            All('Date'),

            'Date'[Date] >= EvaluationStartDate && 'Date'[Date] <= MaxEndDate

        )

Now the company row filter context in the matrix and evaluation start and end dates are respected.

[Anonymous]

Hi @smallpox ,

It's great to hear that your problem has been resolved. Thank you for sharing the solution; it will undoubtedly be helpful to others in the community who may encounter a similar problem. Your contribution is much appreciated.

Best Regards