PowerPivot formula to calculate the number of active projects given start and end dates

[ChrisTW]

I'm new to much of this, so please bear with me. . . I'm trying create a count of active projects by week using projects' start and end dates. I've created a COUNTIFS formula in an Excel worksheet table that works, but I would prefer the calculated column reside in my Data Model/PowerPivot. This should be relatively simple, but being the complete novice that I am, I can't seem to come up with a "DAX" version of the formula. I've tried using COUNT/CALCULATE/FILTER, but I can't seem to get the syntax right. Hoping someone can help!! Thanks!

This is what I came up with in the Excel table:

=COUNTIFS([@[Start Date]],"<="&[@[End of Week]],[@[End Date]],">="&[@[End of Week]])

This is the sample data I've been working with to test my options - The "Weekly Count" column is where my Excel formula currently resides:

[TABLE="width: 500"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Project Name[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]End of Week[/TD]
[TD]Weekly Count[/TD]
[/TR]
[TR]
[TD]Project1[/TD]
[TD]12/1/2017[/TD]
[TD]3/1/2018[/TD]
[TD]12/2/2017[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Project2[/TD]
[TD]12/4/2017[/TD]
[TD]3/4/2018[/TD]
[TD]12/9/2017[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Project3[/TD]
[TD]12/16/2017[/TD]
[TD]3/16/2018[/TD]
[TD]12/16/2017[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Project4[/TD]
[TD]12/18/2017[/TD]
[TD]3/18/2018[/TD]
[TD]12/23/2017[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Project5[/TD]
[TD]12/20/2017[/TD]
[TD]3/20/2018[/TD]
[TD]12/23/2017[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Project6[/TD]
[TD]12/21/2017[/TD]
[TD]3/21/2018[/TD]
[TD]12/23/2017[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Kamolga]

When you do a pivot, you can add acalculated fiel in Analyse tab. You can name it Weekly count and use a formula

=countifs('Start Date',"<="&'End of Week','End Date',">="&'End of Week')

. There is an option to add pivot to databodel when you make one, so I guess the field would (I don't have power pivot to test )

 

[ChrisTW]

Thanks for responding! I may be missing something, but I'm not getting how this would work since "Countifs" can't be used in PowerPivot.

In trying to come up with a formula calculated column in my PowerPivot data model, I stumbled upon the "measure" functionality, which appears to accomplish what I want - It gives me a calculation, or measure in the case, that resides in PowerPivot.

The only challenge at this point is that that I've realized my formula is not quite right.

Measure 1:=CALCULATE(COUNT(Table3[Project Name]),FILTER(Table3,Table3[Start Date]<=Table3[End of Week]),[I][B]FILTER(Table3,Table3[End Date]>Table3[End of Week[/B][/I]]))

My last filter shows "End Date>End of Week," but this should also indicate that the End Date can be BLANK. I'm not sure how to add that condition.

 

[ChrisTW]

Okay, it appears I've figured out the PowerPivot measure formula. Tested in an actual pivot table and it appears to be working as expected. Found that the double pipe || serves as the "OR", and then I used ISBLANK.

Measure 1:=CALCULATE(COUNT(Table3_2[Project Name]),FILTER(Table3_2,Table3_2[Start Date]<=Table3_2[End of Week]),FILTER(Table3_2,Table3_2[End Date]>Table3_2[End of Week][U][B]||ISBLANK(Table3_2[End Date])))[/B][/U]

 

[Kamolga]

Great!!! My computer with power pivot is down so could not test the formula, sorry for the misleading part. Glad it works