DAX HELP CUMULATIVE TOTALS WITH GAPS

[cool_333]

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

[SystemEngineer]

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. 

[v-stephen-msft · Accepted solution]

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.           

[vicky_]

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 
)