Cumulative Total should not start from base value

[Anonymous]

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])

)

)

[Anonymous · Accepted solution]

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

)

[amitchandak]

@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/

[Ashish_Mathur]

Hi,

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