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
