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 this should be based on the past 12 months, so if any accident is older than 1 year, this should not be included in the final value for the specified month.

 

So let's say we had 2 accidents in January, 1 in March, 3 in May, then it should show 2 accidents in January, 2 accidents in February, 3 in March, 3 in April, 6 in May, etc.

 

What would be the smartest way to make it? Thank you!

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)

     

     

9 Replies

  • NickolajJessen's avatar
    NickolajJessen
    Solution Sage
    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?

  • pal95's avatar
    pal95
    Helper III
    4 years ago

    Hi, Sorry but It does not work, it always shows the sum:

     

     

     

    • NickolajJessen's avatar
      NickolajJessen
      Solution Sage
      4 years ago

      Hi,

      You need to filter the year in one way or another. In my provided example i used year & Month on the axis,
      Without a filter on the year, your MAX() will return the maximumdane for ANY of the month. Ex. december will return

       

       

       

      'Calendar'[Date] <= MAX('Calendar'[Date])

       

       

       

      'Calendar'[Date] <= 31-12-2100 instead of the expected 2021.

      Consider using a Slicer

      Consider using the filter pane

      Consider adding a year filter in the measure
      Consider adding Year to axis

  • pal95's avatar
    pal95
    Helper III
    4 years ago

    Okay, I created the Relative Date Column and its working fine now!