Again a interesting question on my ask a questions page, keep them coming 🙂
Luke had a question where he wanted to get a date based calculation:
I have a table of people and a table of project assignments.
The assignments have a person, start date, end date and number of hours per week.
I want to create a pivot that has the date as columns and people as rows, with the total number of assigned hours as values.
I created a table in Excel that recreates this scenario:
Next I created a new table that contains a separate date range:
I loaded both tables into PowerPivot. Because I want to get the total number of hours per day I am unable to work with a relationship between the date table and the fact table. I need to create a formula that searches the fact table for those that rows are appropriate for each date value in the pivottable.
=Calculate( sum(Project[Hours]), FILTER(Project, COUNTROWS(FILTER(VALUES(Dates[Dates]), Project[startdate] <= Dates[Dates] && Project[enddate] >= Dates[Dates] )) > 0) )
This measure does the following:
Calculate the sum of project[hours] for those rows in the Project table that has more than 0 rows in the Date table where Project[startdate] < = ‘Date'[Date] and Project[enddate] >= ‘Date’[Date].
The only downside of this query has to iterate through the entire project table for each row in the date table, is if the Project table is huge this might have big performance impact.
A second question was to create a project start date to current date sum. Again we need to create a measure to do it:
=if(COUNTROWS(VALUES(Dates[dates])) = 1, SUMX(DATESBETWEEN(Dates[dates], FIRSTDATE(Project[StartDate]), VALUES(Dates[dates])) , Project[SumPerDayMeasure]) , BLANK())
What happens here is that we want to sum all the values of the measure per day for each day from the beginning of the project to the current day.
Sumx will sum the values of our previous measure for each day between FIRSTDATE(Project[StartDate]) and VALUES(Dates[dates]), being the current day in the context.