Forum Discussion
Helper III12-month rolling cumulative
Hi akim_no Thank You
Can you please check this measure
Slicer Aware Cumulative =
VAR CurrentDate =
MAX ( 'Calendar'[Date] )VAR SlicerStart =
MINX ( ALLSELECTED ( 'Calendar'[Date] ), 'Calendar'[Date] )RETURN
SUMX (
FILTER (
Orders,
-- Order must overlap slicer at least partially
Orders[StartDate] <= CurrentDate
&& Orders[EndDate] >= SlicerStart
),
VAR EffectiveStart =
MAX ( Orders[StartDate], SlicerStart )VAR EffectiveEnd =
MIN ( Orders[EndDate], CurrentDate )VAR ActiveDays =
IF (
EffectiveStart <= EffectiveEnd,
1 + DATEDIFF ( EffectiveStart, EffectiveEnd, DAY ),
0
)VAR TotalDays =
1 + DATEDIFF ( Orders[StartDate], Orders[EndDate], DAY )VAR DailyAmount =
DIVIDE ( Orders[OrderAmount], TotalDays )RETURN
DailyAmount * ActiveDays
)Cumulative starts at slicer start
Stops exactly at each order’s EndDate
Works with overlapping orders
Works for long slicer ranges (2026, 2027, etc.)
No dependency on Calendar–Order relationship direction
Super UserHi akim_no
Thank you for sharing the scenario example.
Could you please check this measure ?
Slicer-Aware 12-Month Cumulative (Calculate) =
VAR CurrentDate = MAX ( 'Calendar'[Date] )
VAR SlicerStart = MIN ( 'Calendar'[Date] )
VAR SlicerEnd = MAX ( 'Calendar'[Date] )
RETURN
IF (
CurrentDate < SlicerStart || CurrentDate > SlicerEnd,
BLANK(),
CALCULATE (
SUMX (
Orders,
VAR StartDate = Orders[StartDate]
VAR EndDate = EDATE ( StartDate, 12 ) - 1
VAR TotalDays = DATEDIFF ( StartDate, EDATE ( StartDate, 12 ), DAY )
VAR DailyAmount = DIVIDE ( Orders[OrderAmount], TotalDays, 0 )
// Only include if order is active within the cumulative range
VAR CumulativeStart = MAX ( SlicerStart, StartDate )
VAR CumulativeEnd = MIN ( CurrentDate, EndDate, SlicerEnd )
RETURN
IF (
CumulativeStart <= CumulativeEnd,
DailyAmount * ( 1 + DATEDIFF ( CumulativeStart, CumulativeEnd, DAY ) ),
0
)
),
// Remove date filters from Orders table since we're handling dates manually
ALL ( 'Calendar' )
)
)
Helper IIITo illustrate, I’ve included a screenshot. I have a relationship between calendar table and my fact table via Duration date. For each order, I have a start date and an end date.
The cumulative should work as follows: if I select a period from February to June 2026, the cumulative should start from the date selected in the slicer and stop at the order’s end date. In other words, each order contributes to the cumulative during the period that corresponds to the intersection between the selected range and its active lifetime, and stops contributing after its end date.
- krishnakanth240
Hi akim_no Thank You
Can you please check this measure
Slicer Aware Cumulative =
VAR CurrentDate =
MAX ( 'Calendar'[Date] )VAR SlicerStart =
MINX ( ALLSELECTED ( 'Calendar'[Date] ), 'Calendar'[Date] )RETURN
SUMX (
FILTER (
Orders,
-- Order must overlap slicer at least partially
Orders[StartDate] <= CurrentDate
&& Orders[EndDate] >= SlicerStart
),
VAR EffectiveStart =
MAX ( Orders[StartDate], SlicerStart )VAR EffectiveEnd =
MIN ( Orders[EndDate], CurrentDate )VAR ActiveDays =
IF (
EffectiveStart <= EffectiveEnd,
1 + DATEDIFF ( EffectiveStart, EffectiveEnd, DAY ),
0
)VAR TotalDays =
1 + DATEDIFF ( Orders[StartDate], Orders[EndDate], DAY )VAR DailyAmount =
DIVIDE ( Orders[OrderAmount], TotalDays )RETURN
DailyAmount * ActiveDays
)Cumulative starts at slicer start
Stops exactly at each order’s EndDate
Works with overlapping orders
Works for long slicer ranges (2026, 2027, etc.)
No dependency on Calendar–Order relationship direction- v-veshwara-msft
Community Support
Hi akim_no ,
Thanks for reaching out to Microsoft Fabric Community.
Just wanted to check if the response provided by krishnakanth240 was helpful. If further assistance is needed, please reach out.
Thank you.