Monthly and QTD, YTD, MTD on same Pivot

DAX DAX Calculations
1

Hi All

I am sure I have seen a tutorial on this but have failed to find it.

Is it possible in Powerpivot Excel 2016 to have monthly and cumulative measures on the same pivot.

eg
Jan Feb March April June July August September October November December MTD QTD YTD
Any pointers gratefully received.

Thanks

Chris

2

Hi Chris, sorry I don’t use powerpivot that often. Can you not just have multiple measures in the ‘values’ section?

I would have thought this would happen automatically?

3

Hi Sam you could but you have a measure for each month. You would end up having hundreds of measures.thanks for the reply.

Chris

4

Oh right I get it now.

You want them at the end.

Interesting. I’m not sure you can even do this in Power BI, as the matrix visual wouldn’t allow. You would need to create this all individually as measures and have a table visual instead of a matrix.

5

Hi Sam

Solved it with a Switch Function and Disconnected Table

Period VALUE:=IF(HASONEVALUE(Periods[Period Name]),
   SWITCH(VALUES(Periods[Period Name]),
     "W-5",CALCULATE([Availability],Dates[MonthNameShort]="W-5"),
     "W-4", CALCULATE([Availability],Dates[MonthNameShort]="W-4"),
     "W-3", CALCULATE([Availability],Dates[MonthNameShort]="W-3"),
     "W-2", CALCULATE([Availability],Dates[MonthNameShort]="W-2"),
     "W-1", CALCULATE([Availability],Dates[MonthNameShort]="W-1"),
     "B1", [B1],
     "Jan", CALCULATE([Availability],Dates[MonthNameShort]="Jan"),  
     "Feb", CALCULATE([Availability],Dates[MonthNameShort]="Feb"),
     "Mar", CALCULATE([Availability],Dates[MonthNameShort]="Mar"),
     "Apr", CALCULATE([Availability],Dates[MonthNameShort]="Apr"),  
     "May", CALCULATE([Availability],Dates[MonthNameShort]="May"),
     "Jun", CALCULATE([Availability],Dates[MonthNameShort]="Jun"),
     "Jul", CALCULATE([Availability],Dates[MonthNameShort]="Jul"),
     "Aug", CALCULATE([Availability],Dates[MonthNameShort]="Aug"),  
     "Sep", CALCULATE([Availability],Dates[MonthNameShort]="Sep"),
     "Oct", CALCULATE([Availability],Dates[MonthNameShort]="Oct"),
     "Oct", CALCULATE([Availability],Dates[MonthNameShort]="Oct"),  
     "Nov", CALCULATE([Availability],Dates[MonthNameShort]="Nov"),
     "Dec", CALCULATE([Availability],Dates[MonthNameShort]="Dec"),
     "B2", [B2],
     "CMCY", [Mth_CY_Act],
     "CMB", [Mth_CY_Bud],    
     "CMPY", [Mth_PY_Act],
     "YTDCY", [Ytd_CY_Act],
     "YTDB", [Ytd_CY_Bud],    
     "YTDPY", [Ytd_PY_Act],    
         BLANK()     
     ),[Total_Amount])

Cheers

6

Yes that’s right, perfect.

I guess the trade off is the unflexibility of the sort order (but you could create an index I guess)

This is actually a great example of what’s possible in many situations like this.

I’ll put this on the list for videos.

7

Hi Sam

Yes I used an index column as the sort by column. This will be a great video example. My only issue now is how to get multiple measures on rows with this column format.