Forum Discussion

LuizFelipe42's avatar
LuizFelipe42
Regular Visitor
3 years ago

Cumulative sum with filters and initial value

Hello folks!

I'm having a issue with the cumulative sum of number of rows by year.

On my dashboard, i have a column "DATACATEGORIA" (date category), which has the register year, but from 2002 to below, the year number is ommited and, instead, is labeled as "Até 2002" (until 2002) and, from 2003 to above, the year number is shown normally. Example:

Date                    Date Category

01/05/1995         Until 2002
20/04/2001         Until 2002
30/11/2008         2008
15/12/2023         2023

However, as you can see in the chart, the cumulative is calculated as expected, but the sum is recounted when the category changes from "Até 2002" to the respective year.


How can i use the cumulative from "Até 2002" as initial value from the sum from 2003 to above?

Currently i'm using this DAX, so the cumulative sum is affected dinamically by the other filters on the dashboard:

annual_cumulative =
CALCULATE(
    SUM(cadastro[COUNT_LINES]);
    FILTER(
        ALLSELECTED(cadastro[DATACATEGORIA]);
        ISONORAFTER(cadastro[DATACATEGORIA]; MAX(cadastro[DATACATEGORIA]); DESC)
    )
)

 

Thanks for the help!

1 Reply

  • LuizFelipe42 , Create a new column in date  Datetable

     

    Switch(true() ,

    [Date] <= date(2002,12,21) , " Untill 2002",

    format([Date], "YYYY")

     

    If needed create a sort column too.

     

    Use this column on your axis