# Cumulative total question using DAX

Dear all members,

I’m looking for some help in calculating a cumulative total. I tried it as a measure and a calculated column, but it always referred me to the same number. I’ve tried using CALC, SUM, SUMX, and time-based functions - to name a few features that I’ve used.

The recommended filter of [Date] <= MAX [Date] always returns an error. While the Earlier function returns error that there isn’t a function above it. I want to shape and transform my data using DAX in Power BI.

Any help would be greatly appreciated.

Can you share your data sample?

Usually, this is the DAX that Sam recommends in all of his trainings. I have used them and it works fine:

``````Cumulative Amount =
CALCULATE (
[Total Amount],
FILTER ( ALLSELECTED ( Dates ), Dates[Dates] <= MAX ( Dates[Dates] ) )
)``````

This one shouldn’t be too difficult.

Just review the below links in detail and you will learn all you need to know about how to calculate cumulative totals.

https://blog.enterprisedna.co/tag/cumulative-totals/

Plenty of examples here for you to review.

Good luck!

Sam

Hi all,
I was reading this topic looking for help for a similar case I have:

1 Date table for year 2020 with columns e.g. ‘Date’[Date] (mm/dd/yyyy) and ‘Date’[ShortMonth] (“MMM”)
1 Fact table for actual sales from january to february with a date column (end of month format mm/dd/yyyy) linked to the ‘Date’[Date]
1 Fact table for budget dales from january to december with a date column (end of month format mm/dd/yyyy) linked to the ‘Date’[Date]

I want to show cumulative totals YTD for actuals and budget for their respective ‘Date’[ShortMonth]

1/ I have my measure for Actuals YTD as follows which is working good:

NF Actuals YTD =
VAR LastEOM =
FORMAT ( LASTDATE ( ‘Non Fuel’[EOM] ), “MMM” )
RETURN
IF (
SELECTEDVALUE ( ‘Date’[ShortMonth] ) > LastEOM,
BLANK (),
CALCULATE ( [NF Actuals], DATESYTD ( ‘Date’[Date] ) )
)

2/ I want to show the cumulated Budget YTD (until february) and displaying the Budget YTD cumulated amount in the “Total” line.
For now I do not have any filter on the report.

2.1/ 1st try was:
NF BU20 YTD =
IF (
[NF Actuals YTD] > 0,
CALCULATE ( [NF BU20], DATESYTD ( ‘Date’[Date] ) ),
BLANK ()
)

But the “Total” line is showing the total 2020 budget amount instead of YTD.

2.2/ 2nd try was:
NF BU20 YTD 2 =
IF (
LASTDATE ( ‘Date’[Date] ) > TODAY (),
BLANK (),
CALCULATE ( [NF BU20], DATESYTD ( ‘Date’[Date] ) )
)

But the “Total” line shows no amount.

2.3/ 3rd try was:
NF BU20 YTD 7 =
VAR LastEOM =
FORMAT ( LASTDATE ( ‘Non Fuel’[EOM] ), “MMM” )
VAR NFBU20YTD =
CALCULATE ( [NF BU20], DATESYTD ( ‘Date’[Date] ) )
RETURN
IF ( SELECTEDVALUE ( ‘Date’[ShortMonth] ) > LastEOM, BLANK (), NFBU20YTD )

Same issue than 2.2. It shows total 2020 Budget amount.

If I would come to apply a filter, I could use allselected and it would be solved but for now I just want to reflect total YTD Feb for both actuals & budgets with total line showing YTD amounts… Seems to simple again but strugglinh