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)

     

     

pal95's avatar
pal95
Helper III
4 years ago

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?

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.