Forum Discussion

pal95's avatar
pal95
Helper III
4 years ago
Solved

Create a cumulative column from event based table

Hello, I have an event-based table, Accidents, with the following columns: - Date - User - Accident Type - Comment   I would like to create a cumulative line chart from the data above, and...
Need Help
  • NickolajJessen's avatar
    NickolajJessen
    4 years ago
    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)

     

     

NickolajJessen's avatar
NickolajJessen
Solution Sage
4 years ago

Can you be more specific? Current vs. expected results

pal95's avatar
pal95
Helper III
4 years ago

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

  • NickolajJessen's avatar
    NickolajJessen
    Solution Sage
    4 years ago

    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.