Forum Discussion

jdw_msft's avatar
jdw_msft
Frequent Visitor
1 year ago
Solved

Getting sum from cumulative table

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's avatar
    Jihwan_Kim
    1 year ago

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

     

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    1 year ago

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