Forum Discussion
Create a cumulative column from event based table
Cumulative = Var _res =CALCULATE( COUNTROWS(Accidents) ,FILTER(ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]) && 'Calendar'[Date] >= (EOMONTH(MIN('Calendar'[Date]), -13)+1) ) ) Var _End = CALCULATE( COUNTROWS(Accidents) ,FILTER(ALL('Calendar'), 'Calendar'[Date] >= MIN('Calendar'[Date])) ) Return IF(_End>0,_res)
Cumulative =
Var _res =CALCULATE(
COUNTROWS(Accidents)
,FILTER(ALL('Calendar'),
'Calendar'[Date] <= MAX('Calendar'[Date])
&& 'Calendar'[Date] >= (EOMONTH(MIN('Calendar'[Date]), -13)+1)
)
)
Var _End =
CALCULATE(
COUNTROWS(Accidents)
,FILTER(ALL('Calendar'), 'Calendar'[Date] >= MIN('Calendar'[Date]))
)
Return
IF(_End>0,_res)
It works fine but my visual doesn't seem to include only the last 12 months, it takes everything into account. I would like only the past 12 months to be taken into account, so for Jun22, it should include results only until Jun21, could you please help me with that?
Can you be more specific? Current vs. expected results
Hi Nickolaj,
Current Result (almost good):
Error 1 - columns with no info aren't visible (from Apr2021 to Dec2021 it should show "1"), do you know what can be a cause of this?
Expected Result: Rolling 12-month cumulative, so the Mar-2021 result should cause every next column to be 1 for 12 months, and in Apr-2022 it should make the result lower by 1 as it stops counting.
Thank you for your time and patience
Har hard to. Mine does it fine.
What does your measure look like?
Edit:
I see you posted your formula above.
Try using the date in stead of this relative month thing you got going.