Slicer not acting as expected with cumulative totals

[Anonymous]

Hi,
I have a dataset that looks like this:

Worklogs

SpentDate	DataEntryDate	SecondsSpent
26/07/2023	26/07/2023	27000
27/07/2023	30/07/2023	13500
27/07/2023	30/07/2023	13500

RequiredTime

RequiredDate	RequiredSeconds
26/07/2023	27000
27/07/2023	27000

Calendar (created using DAX)

Date	Year	Qtr	QtrNum	Month	MonthNum	MonthYearNum	MonthYear	Day	DayName

Here's the DAX I used:

Calendar = 
VAR _calendar = CALENDARAUTO()
RETURN
    ADDCOLUMNS(
        _calendar,
        "Year", YEAR([Date]),
        "Qtr", "Q"& FORMAT([Date],"Q"),
        "QtrYear", "Q"& FORMAT([Date],"Q yyyy"),
        "QtrNum", FORMAT([Date],"Q"),
        "Month", FORMAT([Date],"mmmm"),
        "MonthNum", MONTH([Date]),
        "MonthYearNumber",FORMAT([Date],"yymm"),
        "MonthYear",FORMAT([Date],"mmm yyyy"),
        "Day",Format([Date],"dd"),
        "DayName",Format([Date],"dddd"
    ))

I have created a date hierarchy from this table that is like this:

• Dates
  • Year
  • QtrYear
  • MonthYear
  • Day

I have the following measures:

Spent (d) = 
DIVIDE(SUM('Worklogs'[SecondsSpent]),27000)

Cumulative Days Spent (SpentDate)  =
CALCULATE(
    [Spent (d)],
    USERELATIONSHIP(Worklogs[SpentDate],'Calendar'[Date]),
    FILTER(ALL('Calendar'[Date]),'Calendar'[Date] <= MAX('Calendar'[Date])))

Cumulative Days Spent (DataEntryDate) = 
CALCULATE(
    [Spent (d)],
    USERELATIONSHIP(Worklogs[DataEntryDate],'Calendar'[Date]),
    FILTER(ALL('Calendar'[Date]),'Calendar'[Date] <= MAX('Calendar'[Date])))

Expected (d) = 
DIVIDE(SUM(RequiredTime[RequiredSeconds]),27000)

Cumulative Expected (d) = 
CALCULATE(
    [Expected (d)],
    USERELATIONSHIP(RequiredTime[RequiredDate],'Calendar'[Date]),
    FILTER(ALL('Calendar'[Date]),'Calendar'[Date] <= MAX('Calendar'[Date])))

I want to create a line chart that plots the Date on the x-axis and all three cumulative values on the y-axis. I'd then like to use a slicer for the date so that I can see a single month in isolation.

The line chart seems to work OK when not using a slicer, but it shows far too much data to be valuable for my use case - the data goes back ~10 years in the Worklogs table, but only ~5 years in the RequiredTime table.

When I use the slicer (the date hierarchy from the calendar table), the Spent (d) and Expected (d) values are returned correctly, but the cumulative values seem to calculate by summing all values up to and including the date selected. I'd like the cumulative values to only sum values for the month selected in the slicer.

Thanks in advance.

Sean