Only show Months for some Calculations

Anything related to PowerPivot and DAX Formuale
Peter079
Posts: 4
Joined: Mon Jul 24, 2017 7:58 pm

Only show Months for some Calculations

Postby Peter079 » Tue Feb 13, 2018 10:28 pm

Hi Matt, you have provided me with invaluable assistance in the past, and I was hoping that you can solve the following:

I have created a Pivot Table with measures for Actuals, Budgets, Actual YTD and Budget YTD. I am trying to create a report that shows Actuals by Month i.e. Jan, Feb, Mar etc and Actual YTD and Budget YTD only for the current period. My problem is that whenever I put Months on the Pivot (rows or columns) the Pivot applies these Months to the Actual and Budget YTD as well. Do you know of a way in which I can create the report that only shows months for Actuals and not for the YTD measures.

Thanks

Peter

benholmes
Posts: 9
Joined: Tue Jan 30, 2018 9:22 pm

Re: Only show Months for some Calculations

Postby benholmes » Wed Feb 14, 2018 8:31 am

Assuming you want blanks for the YTD measures in the non 'last' months on your PT, one way would be to have a Boolean measure that checks if the month in the current row on the PT is the last month and then incorporate this into your YTD measure calcs. You could do this using ALLSELECTED on your Month field with something like LASTDATE(ALLSELECTED(Calendar(Month End]))=LASTDATE(Calendar[Month End])

If you've got Jul, Aug and Sep on your PT this will compare the last date of all of those months to the last date of each row (ie Jul: does 30/9 = 31/7 FALSE; Aug does 30/9 = 31/8 FALSE; Sep does 30/9 = 30/9 TRUE).

Obviously it depends on your data setup etc, and there may be much more efficient ways to do this, but the attached could work.

Example.xlsx
(229.93 KiB) Downloaded 156 times


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 6 guests