PowerPivot DAX case: Resouce planning report with selecting a date parameter

By | May 23, 2011

I had an interesting question on my ask a questions page, one that I have (or something similar) heard a few times before. So I decided to do make a blog post on how to solve it

Our company tracks changes in the resource planning using a data warehouse. An example would be:

Assignment 1: Ian 8 hours planned for 3-Jun; valid from 1-jan-2011 until 1-feb-2011.
Assignment 1: Ian 6 hours planned for 3-Jun; valid from 2-feb-2011 until 15-feb-2011.
Assignment 1: Ian 7 hours planned for 3-jun; valid from 16-feb-2011 until infinity.

I what to enable our end-user to select any date level (Date, Week, Month, year) and get the current planning for that planned date (3-jun).

First of all I created a table in Excel to represent the data:

Next I create a date table that I can use to select dates at different levels using a slicer:

I did not create a relationship between the two tables because I want to use the values of the date table to search for values in the planning table. So far i have prepared the tables, next is creating the report.

First I created a workbook and a new pivottable with the slicers from the date table and put the employee name on rows:

 

now for the interesting part, on to the DAX formula. We want to use the values from the slicers to determine the date we want to search the planning table on. We can use values(columnname) to get the current value of the slicer and create a date from that on the fly. For example values(‘Date'[year]) will get the current value of years from the slicer. IF you join all 3 together you can create a new Date: Date(values(‘Date'[year]),values(‘Date'[Month]),values(‘Date'[Day]))

Slicers can have multiple values selected at the same time so we need to make sure that only one is selected at the same time, otherwhise we cannot create this date. We can do this with the following formula’s:

=if( countrows(values(‘Date'[year])) = 1 &&countrows(values(‘Date'[Month])) = 1 &&countrows(values(‘Date'[Day])) = 1,

Now on to creating the DAX formula to finding the right values in the planning table:

=if( countrows(values('Date'[year])) = 1 
&&
countrows(values('Date'[Month])) = 1 
&&
countrows(values('Date'[Day])) = 1,
CALCULATE(sum(Rates[Hours]), 
	FILTER(Rates, 
	Rates[Startdate] <= Date(values('Date'[year]),values('Date'[Month]),values('Date'[Day])) 
	&& 
	(Rates[Enddate] >= Date(values('Date'[year]),values('Date'[Month]),values('Date'[Day])) 
	|| ISBLANK(Rates[Enddate]))
	)
	)
,Blank()
)

Let’s look at this step by step:

First we want to calculate the sum of Hours.

CALCULATE (sum(Rates[Hours]),

for the rows in the selected date range:

FILTER(Rates,
Rates[Startdate] <= Date(values(‘Date'[year]),values(‘Date'[Month]),values(‘Date'[Day]))
&&
(Rates[Enddate] >= Date(values(‘Date'[year]),values(‘Date'[Month]),values(‘Date'[Day])) || ISBLANK(Rates[Enddate])) )

Filter the Rates table to return only the rows where
the Rates[Startdate] <= the selected date
AND
Rates[Enddate] >=the selected date OR the Rates[Enddate] is empty.

When this formula is used we get the value we wanted based on the slicer we selected:

Make sure you turn the following “Visually indicate items with no data”  for each slicer (right mouse click, slicer settings):

 

Another blog post with a similar question and answer you can find here: http://www.powerpivotblog.nl/get-values-within-a-start-and-end-date-using-powerpivot-dax

 

  • Ian

    Hi Kasper,

    Thank you for this blog entry. The formula is very usefull for any scenario where you want to do “point in time” analysis.

    I do, however, have a challenging point. The user is now required to select a specific date, would it be possible to let the user select a month or week (in combination with a year) without selecting a date.

    Example: I want to see the current planning in february 2011 (user selects year 2011 and month 2 (feb) in the slicers, not selecting any date in the third slicer). This would return 7 hours for Ian and 8 hours for Jeff. This might be a bit to complex for DAX, seeing as our calculation parameters change depending on which level of the date dimension (meaning month, year or week) the user wants to see the planning in hours.

    I am going to try to see if I can figure it out based on you formula. I will keep you posted should I be able to solve it.

    Again, thank you for your time and effort.

    Ian

  • Ian

    I think I might have a solution. The answer to my question stated above lies in extending the IF statement in your formula.

    If the user selects a month, the date to which we compare is the last date of the selected month (in the selected year). If the user selects a week, the date to which we compare is the last date of that week (and year).

  • Kasper de Jonge

    Hi @Ian ,

    Yes you are right, using the if statement could create a more dynamic measure.
    for example:
    =if( countrows(values(‘Date'[year])) = 1
    &&
    countrows(values(‘Date'[Month])) = 1
    &&
    countrows(values(‘Date'[Day])) = 1,
    Single date formula
    ,
    if( countrows(values(‘Date'[year])) = 1
    &&
    countrows(values(‘Date'[Month])) > 1
    ,
    Single year statement
    ,
    etc etc
    )

    It would eventually become a pretty large formula but you can do it.

  • Hi Kasper, just wanted to let you know that I blogged the proposed solution.

    http://smithicus.wordpress.com/2011/06/05/solving-the-point-in-time-problem-with-powerpivot/

  • Eduardo Schmidt

    I have one problem in percentage calculation . I need that my result is 2,4% but brings 247,48%. I know there “format button” in ribbon but this not help. Thanks