Slicer not affecting measure

[LostintheBIu]

Hi everyone,

I created a calculated sum measure that has a date condition, and my issue is that this date condition is causing my measure to ignore the date slicer I have in the report page. I need a measure that has a date condition and also still responds to my date slicer.

Here is the measure, which sums up actuals on Account 20000, but only for dates up to the most recent Account 10000 posting date. 

Account 20000 Actuals =

var LatestAccount10000PostedDate = CALCULATE(
MAX(DateTable[Dates]),'Actuals'[Account]=10000)

RETURN

CALCULATE([Actuals sum measure],
'Actuals'[Account]=20000,
'DateTable'[Dates]<=LatestAccount10000PostedDate
)

How can the measure be updated so it also affected by the date slicer that I have on the report page? For example, the most recent Account 10000 posting date was 28th February 2025, so the measure only filters for dates up to that date as a maximum. But then I also want to be able to use the date slicer, fx filter the slicer for 2025, so I get the actuals between 1st and 28th February 2025, or if I filter the slicer for 2024, I just get the actuals for the full 2024 (because all those dates are less than 28th February 2025).

Thanks in advance for any help.

[DataNinja777 · Accepted solution]

Hi @LostintheBIu ,

The issue arises because the LatestAccount10000PostedDate variable is calculated without considering the date slicer, which results in a fixed upper boundary that ignores the slicer selection. To make the measure dynamic while ensuring it remains responsive to the slicer, you can define the slicer range using MIN and MAX functions while ensuring the end date does not exceed LatestAccount10000PostedDate. The updated measure is:

Account 20000 Actuals =
VAR LatestAccount10000PostedDate = 
    CALCULATE(
        MAX(DateTable[Dates]), 
        'Actuals'[Account] = 10000
    )

VAR SlicerStartDate = MIN(DateTable[Dates])
VAR SlicerEndDate = MAX(DateTable[Dates])

RETURN
CALCULATE(
    [Actuals sum measure],
    'Actuals'[Account] = 20000,
    'DateTable'[Dates] >= SlicerStartDate,
    'DateTable'[Dates] <= MIN(SlicerEndDate, LatestAccount10000PostedDate) 
)

This ensures that the measure dynamically filters actuals for Account 20000 within the slicer’s selected date range while capping the end date at the most recent Account 10000 posting date. If the slicer is set to February 2025, it returns actuals from February 1st to 28th, while a slicer selection for 2024 will return all 2024 actuals. If the slicer is set beyond February 28, 2025, no values will be returned since it exceeds the latest posting date. This approach maintains both the slicer’s influence and the upper boundary condition.

Best regards,

[LostintheBIu]

Worked perfectly, thank you! 🙂

[scn]

1. Remove the date filter from this measure. Having this might not help the slicer.

2. Create a new measure for the LatestAccount10000PostedDate. Use this to filter the slicer.