## 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

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

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

### Who is online

Users browsing this forum: Google [Bot] and 4 guests