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.
Thanks for a good post, very helpful post
I found some interesting things when working around this. When we have all the columns in a single utility table and suppose there are few measures depending on those columns we will not be able to complete any of the measure. For example let us assume there is a utility table with columns A and B as below:
Utility Table:
Col A Col B
1 1
2 2
3 3
4 4
Suppose we have the two measures as defined below:
CALCULATE(SUM([QTY Issued From Store]),
DATESINPERIOD(‘Date'[Date],
LASTDATE(‘Date'[Date]),
0-MAX(‘Utility Table’[ColA]),
MONTH))
CALCULATE(SUM([QTY Issued From Store]),
DATESINPERIOD(‘Date'[Date],
LASTDATE(‘Date'[Date]),
0-MAX(‘Utility Table’[ColB]),
MONTH))
Next we are trying to utilize these two measures at a single time…This doesn’t work out for some strange reason. Either we get #NUM! values or errors. I wonder if there is a turn around.
Hey!
How do you do configure this measure:
=Calculate(
sum(Project[Hours]),
FILTER(Project,
COUNTROWS(FILTER(VALUES(Dates[Dates]),
Project[startdate] = Dates[Dates] ))
> 0)
)
so it will only do the measure for the workdays?
Best regards!
As you said, it is having huge performance issue. Any alternative formula or method to find the rows between the dates. I am looking for alternative for
=Calculate(
sum(Project[Hours]),
FILTER(Project,
COUNTROWS(FILTER(VALUES(Dates[Dates]),
Project[startdate] = Dates[Dates] ))
> 0)
)
Hi thx. I tried this and it works. Only it’s driving me nuts I don’t understand how it works. Could anybody take the effort to explain this DAX function step by step? I’m referring to the first DAX formula, espacially the “countrows(filter(values(” part.
Many thx in advance!
OK here we go, that functions does the following:
calculate the sum of Project hours
for each row in the project table
that has rows where the dates[date] value corresponding to the row in the project table (using the relationship) falls between start and end date.
Hope that helps.
I hate to dig up an old thread, but this is exactly what I’ve been looking for.
I’ve recreated the model shown above, but I must be missing the joins between Project & dates to get this to work correctly. I’m assuming the row label is a date from the date table and I’ve joined start date to dates. What else am I missing? It isn’t spreading for me like it does in the example above.
OK, I figured out that if you don’t do a join at all it works. Cool stuff! Now, how do you do it with a joined date table? That way I can use the rest of the date features…
Old question, new answer. Create the relationship as Inactive. Then Userelationship for the date features.
thread resurrection… i also have a time dimension that needs to be considered anyone have any bright ideas?
how can i do the average of the date from to columns first in days and second in moths ?
I don’t believe there’s a DAX function to work with insects
Hello
I have the same problem, but between the two tables I have an active relationship ( startdate = dates).
In applying the formula j can’t display all dates between the start date and end date.
Any solution ?
Best regard
I have a similar question. I need a DAX solution. Assume the fist blue table.
How can I take a total and dis aggregate it evenly over any date range?