Search code examples
daxpowerbi-desktopcumulative-sumcumulative-line-chart

Cumulative sum with time-intelligent slicer using dax in powerbi

I'm new to Power BI, and I'm working on this measure to show cumulative sum on specific time period.

The case and what I want:

I have one Calendar table [DateKey], and main data table [Data], they have relationship based on column date. I need to show a visual of line & clustered column chart and a time slicer, in which cumulative sum of revenue based on the slicer.

For eg: I have revenue table for July, I put out the DateKey[Month] column as slicer, when I choose July, the visual would show the cumulative sum of Revenue in July (from 1st to 31st July) When I choose August, the visual would show cumulative sum Revenue in August (from 1st to 31st August)

What I tried: I used the following DAX

Cumulative Sum Rev = CALCULATE(
SUM(Data[Revenue]),
FILTER(
ALL(Data),
Data[Date]<=MAX(Data[Date])
)
)

Actual outcome: It did create a cumulative sum line, but it was fine for July. If I choose August in slicer, it would be cumulative sum from July to August. What I expected is the cumulative sum will begin from August, not from July.

I tried another solution, which is using ALLEXCEPT instead of ALL, but it does seems not working for date column (you can see my measures in the pbix files already have it, but to filter another column [Lead_type], which works perfectly fine for another slicer)

Cumulative Sum Rev = CALCULATE(
SUM(Data[Revenue]),
FILTER(
ALLEXCEPT(Data,Data[Lead_type]),
Data[Date]<=MAX(Data[Date])
)
)

Please help to show me where I was wrong.

Here is the link to my pbix file, it include what my visual would be and my measure: https://1drv.ms/u/s!As4H0zrXywmbhaVFDprZ6RJmFUMbbg?e=l4Wxe5

Solution

  • What you are after is a "Month-to-date" measure. This is a built-in time intelligence function in DAX - you can also make your own version using the correct variables and DAX syntax - this carries more flexibility. However, for this case, this should do the trick:

    First add a measure that sums the Revenue column. It is always smart to have these simple aggregating measures - when you use these measures in other measures, they are wrapped in a CALCULATE statement automatically, and this avoids some confusion down the road:

    Revenue Amount := SUM ( Data[Revenue] )

    Then add a TOTALMTD measure, which takes the first measure as the first argument, and your date column as a second argument. Note: the date column is in your DateKey table - do not use the date column in your fact table for this:

    Cumulative sum = 
TOTALMTD (
    [Revenue Amount] , 
    DateKey[Date]
)

    You should also read up a bit on calendar tables and the difference between creating a table using ADDCOLUMNS and adding individual calculated columns on top of a calculated table. I would start here: https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/