Getting sum from cumulative table

[jdw_msft]

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

I'll describe the table columns:

1. date : obviously date
2. sales: sold thing
3. sales_running_sum: this is for monthly sales, reset to 0 every month (when the month change)
4. 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?

The dates column act as a slicerthe visual slicer

JanuaryFebruaryMarch

[Jihwan_Kim · Accepted solution]

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