Running total in DAX

To get a running total in DAX you can use the DATESYTD function.

Here is an example that gives running total per day for a given range.

EVALUATE
FILTER (
    ADDCOLUMNS (
        VALUES ( 'Date'[Date] ),
        "Running total", CALCULATE (
            'Internet Sales'[Internet Total Sales],
            DATESYTD ( 'Date'[Date] )
        ),
        "Sales per day", 'Internet Sales'[Internet Total Sales]
    ),
    'Date'[Date] < DATE ( 2006, 01, 20 )
        && 'Date'[Date] >= DATE ( 2006, 01, 01 )
)

And here is an example on how to get running total per month.

EVALUATE
FILTER (
SUMMARIZE (
'Date',
'Date'[Month Name],
'Date'[Calendar Year],
"Running total", CALCULATE (
'Internet Sales'[Internet Total Sales],
DATESYTD ( 'Date'[Date] )
),
"Sales per month", 'Internet Sales'[Internet Total Sales]
),
'Date'[Calendar Year] = 2006
)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s