Count based on Date

[IndyDoF]

Hello, I have been trying to get a count of records based on 2 date fields matched against a date table. I have been able to do this with just a data table and pivot table using formulas but have not been successful doing the same thing from power pivot.

I want to determine the number of calls that were open in a specified date range.

My fact table records contains "Call_Entered_Date" and "Call_Closed_Date" DateTime fields for each record. The formula to determine if a call is open is simply "(Call_Entered_Date < DateKey) AND (Call_Closed_Date > DateKey)"

What I would like to see is a pivot table using a selected date range as follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]1/1/2013[/TD]
[TD]110[/TD]
[/TR]
[TR]
[TD]2/1/2013[/TD]
[TD]120[/TD]
[/TR]
</tbody>[/TABLE]

I have been trying to use the calculate function but I dont seem to be able to use the DateKey in the filter.

Any suggestions would be most appreciated

 

[prez02]

OK, I assume your fact table looks like this :

CallID Call_Entered_Date Call_Closed_Date .... .... ....
CallIDNr1 1.1.2012 2.1.2012

Have you go a separate Dates table? If not, create one like this and add it to your model. I called mine Dates

Datekey
1/1/2012
2/1/2012
....

Then, this measure calculates the open calls for each date, assuming you have your datekey on rows in the pivot.

if(HASONEVALUE('Dates'[Datekey]);
Calculate(COUNTA(Fact[CallID]);
values('Dates'[Datekey]) > Fact[Call_Entered_Date];
values('Dates'[Datekey]) < fact[call_closed_date]))

You could add different aggregation levels like Month and Year to your Dates Table, however that would require some tweaking of the measure. Right now it only accepts a single Date as "input".
Shouldn't all the calls entered at a date and not closed on the same day also be counted, which means >= Call_Entered_Date?

take care
Carsten

 

[Jacob Barnett]

IndyDoF,

Carsten's measure absolutely works but will give you the right answer only if your date table is not related to your fact table. If this is a single purpose model you are trying to set up that isn't a problem however best practice is to relate your date table to your fact table(s) so you can make use of the full potential of PowerPivot's amazing date capabilities: mashups, time intelligence functions and custom calendars.

Once you relate your fact and calendar tables, most likely on [Call_Entered_Date] then you will need to use something a bit more spicy to get it to work like:

=Calculate(
COUNTA( Fact[CallID]),
ALL(Fact),
FILTER( ALL( Fact[Call_entered_date]), Fact[Call_Entered_Date] <= max(Dates[Datekey] )),
FILTER( ALL( Fact[Call_Closed_Date]), Fact[Call_Closed_Date] > max(Dates[DateKey]))
)

This is slightly counter intuitive (to me at least) because normally we would use the date table to filter the fact table based n relationships but here we effectively ignore the relationships and FILTER() the fact table based on the date specified.

The caveat is that if we are talking about a fact table that is millions of rows I would be concerned about performance as its generally not advised to FILTER() your fact table. I may have one more FILTER(ALL()) in here than I strictly need but I think it makes it easier conceptually.

Hope this helps.
Jacob

 

[IndyDoF]

Thanks Prez02 and Jacob,

I did have some success using Prez02's suggestion but as my fact table was related to the date table it was grouping the results as you mention Jacob. I have been trying to find a way around it and I will try your suggestion now. Thanks, this is great!

 

[IndyDoF]

That did it! Works perfectly. Thank you very much. Fact table is < 100,000 records and performance seems good. Here is my final formula:

=CALCULATE(COUNTA(Calls[Call_id]),ALL(Calls),FILTER(ALL(Calls[Call_entered_date]),Calls[Call_entered_date]<MAX(BasicCalendarEnglish[DateKey])),FILTER(ALL(Calls[Call_closed_date]), Calls[Call_closed_date] > MAX(BasicCalendarEnglish[DateKey])))