topic Re: DAX HELP CUMULATIVE TOTALS WITH GAPS in DAX Commands and Tips

DAX HELP CUMULATIVE TOTALS WITH GAPS

cool_333 — Tue, 23 May 2023 23:54:46 GMT

Hi All,

I'm trying to calculate the cumulative total but want to limit it as per max actual date and I don't want the cumulative total to be carried forward after 18/05/2023, it should only show up to 6.70.

Thank you

Re: DAX HELP CUMULATIVE TOTALS WITH GAPS

vicky_ — Wed, 24 May 2023 01:36:04 GMT

You can grab the max date with a value and use that as part of the filter arguments.

I'm assuming that Daily actual total units is a column and not a measure.

Cumulative daily total = var maxDateWithDailyActualTotalUnits = CALCULATE(MAX([Date]), NOT(ISBLANK([Daily Actual Total Units]))) // your calculations return CALCULATE( // your calculations for cumulative sum && [Date] <= maxDateWithDailyActualTotalUnits )

Re: DAX HELP CUMULATIVE TOTALS WITH GAPS

Anonymous — Thu, 25 May 2023 09:15:02 GMT

Hi cool_333 ,

You could create a measure.

Cum.Daily Acutal Units = IF(ISBLANK(MAX('Table'[Daily Acutal Units Total])),BLANK(),CALCULATE(SUM('Table'[Daily Acutal Units Total]),FILTER(ALL('Table'),[Date]<=MAX('Table'[Date]))))

I use the IF statement to determine whether the value is null or not, and if it is not null, it returns a cumulative value. In your case, you need to use the ISBLANK function, not the BLANK function.

You could also download the attachment for mroe details.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DAX HELP CUMULATIVE TOTALS WITH GAPS

SystemEngineer — Mon, 20 Nov 2023 17:40:21 GMT

I have run into issues witht the same situation where using your equations did not work on my data.

If instead of the 0 value entries, you dont' have one (common if you are grabbing data from an entry system, it may not happen every day) and plotting Month-Day for date under hiearchy, instead of letting PBI autoscale for you, you will see gaps in your graphs.

This is what I am trying to prevent.