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

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