Cumulative total by Date and Category Measure not working

[bo_wang5]

Hi All,

I am trying to write a measure to calcuate cumulative totals by date and category.  I'm not sure what I am missing. the top graphic shows visualization for Amount Total which behaves correctly.  However when I try to show cumulative totals by subcategory its not correct.  Thanks for any tips on the measure thanks.

Amount Total = 

sum('Sales Data'[Amount])

Cumulative Total =

Calculate([Amount Total],

Filter(ALLSELECTED('Sales Data'),

'Sales Data'[Close Date]<=MAX('Sales Data'[Close Date])

))

[v-jayw-msft]

Hi @bo_wang5 ,

You are using [Close Date] in your formula, you will need to put it into Rows field.

If it doesn't work, please share some sample data so that we could test the formulas.

Best Regards,

Jay

[TheoC]

Hi @bo_wang5 

Can you try use something like:

Measure = CALCULATE ( [Amount Total] , DATESYTD ( 'Date'[Date] )

Given that the Business Units and the Months are already categorised in the Matrix Table, you shouldn't have any issues using the above measure (unless you don't have a Date table?).

If you don't have a Date table, you can use the following:

Measure = TOTALYTD ( [Amount Total] , 'Sales Data'[Close Date] )

Hope this helps 🙂

Theo