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: date : obviously date sales: sold thing sales_running_sum: this is for monthly sales, reset to 0 every m...
  • 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] )

     

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