Cumulative total question using DAX

DAX DAX Calculations
1

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.

2

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

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

4

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.

image

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 :confused:

Would appreciate your support!
Thanks & regards.
Alex

5

Sorry for the delay.

On this I really recommend running through all the cumulative total resources available at Enterprise DNA. Almost any scenario will be covered for what you need.

https://forum.enterprisedna.co/search?expanded=true&q=%23enterprise-dna-online%20cumulative%20total

In future a demo model should be added if you want specific support on this type of question.

Thanks
Sam