topic Re: Getting sum from cumulative table in DAX Commands and Tips

Getting sum from cumulative table

jdw_msft — Mon, 09 Sep 2024 05:29:05 GMT

Hello everyone, i have a sample table like this picture:

I'll describe the table columns:

date : obviously date
sales: sold thing
sales_running_sum: this is for monthly sales, reset to 0 every month (when the month change)
yearly_running_sum: this is for yearly/annual sales, reset to 0 every year (when the year change)

I have a problem, whenever i use TOTALYTD DAX for yearly_running_sum and TOTALMTD DAX for sales_running_sum. The sum is exponentially large, what i want is when i choose February 10, 2024 it'd show 40 for sales_running_sum and 144 for yearly_running_sum. Is there any implementation to get result that i want?

Re: Getting sum from cumulative table

Jihwan_Kim — Mon, 09 Sep 2024 06:04:40 GMT

Hi,

I tried to create a sample pbix file like below, and please check the below picture and the attached pbix file.

Sales MTD: = VAR _t = FILTER ( ADDCOLUMNS ( ALL ( 'date'[date], 'date'[Year], 'date'[Month], 'date'[Day] ), "@sales", [Sales:] ), 'date'[Year] = MAX ( 'date'[Year] ) && 'date'[Month] = MAX ( 'date'[Month] ) && 'date'[date] <= MAX ( 'date'[date] ) ) RETURN SUMX ( _t, [@sales] )

Sales YTD: = VAR _t = FILTER ( ADDCOLUMNS ( ALL ( 'date'[date], 'date'[Year], 'date'[Month], 'date'[Day] ), "@sales", [Sales:] ), 'date'[Year] = MAX ( 'date'[Year] ) && 'date'[date] <= MAX ( 'date'[date] ) ) RETURN SUMX ( _t, [@sales] )