# Use PowerPivot DAX to get values within a Start and End date

By | April 3, 2011

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

• Sandeep

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.

• Sven

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.

• Kasper de Jonge

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.