Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cumulative Total should not start from base value

Hi, 

 

I am interested in calculating cumulative value. However, when I filter by date, the value again starts from the base value of that month, continuing cumulatively (red). 

What I actually want is the green diagram. That is, although we filter, it remembers the cumulative values for the previous period. Filter should not affect the start.

 

I used the following equation for the red diagram. What should I change to plot the green one?

 

Total Events-Cumulative2 =
CALCULATE(
[Total Events],
FILTER(
ALLSELECTED(data),
data[Event Date] <= MAX(data[Event Date])
)
)
 

 
 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Just found my solution. FYI:

     

    Total Events-Cumulative3 =
    VAR MaxDate = MAX ( data[Event Date] )    -- Saves the last visible date
    RETURN
    CALCULATE (
    [Total Events],                                                   -- Computes sales amount
    data[Event Date] <= MaxDate,                         -- Where date is before the last visible date
    ALL ( 'Calendar' )                                               -- Removes any other filters from Date
    )

     

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

    Share a simple dataset, describe the question and show the result in a Table format.

     

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous ,In case you want a cumulative only for month qtr or year. You can use Time intelligence with date calendar

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Just found my solution. FYI:

     

    Total Events-Cumulative3 =
    VAR MaxDate = MAX ( data[Event Date] )    -- Saves the last visible date
    RETURN
    CALCULATE (
    [Total Events],                                                   -- Computes sales amount
    data[Event Date] <= MaxDate,                         -- Where date is before the last visible date
    ALL ( 'Calendar' )                                               -- Removes any other filters from Date
    )