I have these tables:
DimDate =
DATATABLE (
"Date", DATETIME,
"First of Month", DATETIME,
{
{ "01-01-2024", "01-01-2024" },
{ "01-02-2024", "01-02-2024" },
{ "01-03-2024", "01-03-2024" },
{ "01-04-2024", "01-04-2024" },
{ "01-05-2024", "01-05-2024" }
}
)
And this Fact:
Fact =
DATATABLE (
"Date", DATETIME,
"Amount", INTEGER,
{
{ "01-01-2024", 10 },
{ "01-02-2024", 20 },
{ "01-03-2024", 30 },
{ "01-04-2024", 40 },
{ "01-05-2024", 50 }
}
)
With a simple relationship between the two:
I have this simple measure:
Amount = sum('Fact'[Amount])
And this cumulative measure
Amount Rolling =
VAR MNX = MINX( ALLSELECTED( 'DimDate'[Date] ), 'DimDate'[Date])
RETURN
CALCULATE (
[Amount],
FILTER (
ALL( 'DimDate'[Date] ),
'DimDate'[Date] >= MNX &&
'DimDate'[Date] <= MAX ( 'DimDate'[Date] )
)
)
On the canvas is the following visuals:
I want the measure to return 70 rather than 40 – so the minimum date of the slider is 01-03-2024 so I want the measure to always return the cumulative total from the minimum date of the slider – effectively the sum of this window:
I understand that the slicer for First of the month is having an impact but is there any way to adjust the DAX to give me the result I want?