How to Sum Month To Date and Quarter to Date

[largeselection]

Hello,

I have a dataset created through powerpivot. I'm wondering how I can create a pivot table which will display the sum of orders for the week I select, the month to date including that week, and the quarter to date including that week. The data is arranged by week so I have Jan Week 1, Jan week 2, Jan Week 3, etc. to Feb Week 3. Say for example I select Feb Week 3 in the filter. I'd like to see the sum of orders for Feb Week 3, as well as the sum of orders for Feb Week 1 + Feb Week 2 + Feb Week 3 (Month to date), as well as the sum of orders for Jan Week 1 -> Feb Week 3 (Quarter to date).

I can do these things individually not in the same pivot table by just selecting all of the weeks, but I was wondering if it would be possible to do this by selecting one week only.

I envision something like:
Week Sales = Sum of orders for week selected
Month to Date Sales = Sum of orders for any weeks in the same Month up to the week selected
Quarter to Date Sales = Sum of orders for any weeks in the same quarter up to the week selected

I don't know how to put that into dax though (or if that is even possible). Sorry if this is a silly question, but I'm just getting started in the world of powerpivot.

Thanks in advance for any help/guidance you can provide!

 

[FranzV]

DAX includes time intelligence functions for this; TOTALMTD, TOTALQTD. You need a Calendar Table to use them. Matt Allington at Excelerator BI wrote a post about Power Pivot Calendar Tables that might help you understand how they work. After loading a Calendar Table to your model and relating it to your data table, you can write 3 measures to display what you need in a pivot table.

Sales:= SUM( YourDataTable[Sales] )

Sales MTD:=TOTALMTD( [Sales] , 'Calendar'[Date] )

Sales QTD:=TOTALQTD( [Sales] , 'Calendar'[Date] )

The first will sum all the sales within the current filter context. The MTD, and QTD versions calculate the same sum of sales but for dates between the start of month/quarter and the last date in the current filter context.

If you are interested in exploiting the full potential of Power Pivot, try a book to guide you through your experimentation and learning process. I used Marco Russo and Alberto Ferrari's The Definitive Guide to DAX, but I am sure Matt Allington's Learn to Write DAX or Rob Collie and Avi Singh's Power Pivot and Power BI are also great resources and probably easier reads.