Home > DAX > DAX – Running Totals

DAX – Running Totals

In any business domain, Reporting measures over time is a well-known requirement. Here planning to write commonly used Monthly, Quarterly and Yearly totals out of many DAX time intelligence functions.

MTD: Running totals of every month
QTD: Running totals of every quarter
YTD: Running totals of every year

define measure 'Internet Sales'[MTDAmount] = TOTALMTD(sum('Internet Sales'[Sales Amount]),'Date'[Date])
measure 'Internet Sales'[QTDAmount] = TOTALQTD(sum('Internet Sales'[Sales Amount]),'Date'[Date])
measure 'Internet Sales'[YTDAmount] = TOTALYTD(sum('Internet Sales'[Sales Amount]),'Date'[Date])
EVALUATE (
	 FILTER (
	   SUMMARIZE(
				'Internet Sales'
				, 'Date'[Date]
				, 'Date'[Calendar Year]
				, "Internet Sales"
				, FORMAT(SUM('Internet Sales'[Sales Amount]),"currency")
				, "Sales MTD"
				,FORMAT('Internet Sales'[MTDAmount],"currency")
				, "Sales QTD"
				,FORMAT('Internet Sales'[QTDAmount],"currency")
				, "Sales YTD"
				,FORMAT('Internet Sales'[YTDAmount],"currency")
				 )
			 , 'Date'[Calendar Year] = 2008
			 )
        )
		ORDER BY 'Date'[Date] ASC




Points to consider:

1. Discussed a common requirement scenario
2. We have many DAX Time Intelligence functions for various requirements and I think, selection of function is purely depends on business needs

Advertisements
Categories: DAX Tags:
  1. May 14, 2012 at 7:08 pm

    Wow, superb blog layout! How long have you been blogging for? you make blogging look easy. The overall look of your site is magnificent, as well as the content!

  1. No trackbacks yet.

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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: