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

ChrisTW

New Member
Joined
Nov 10, 2018
Messages
3
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:

Code:
=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:

Project NameStart DateEnd DateEnd of WeekWeekly Count
Project112/1/20173/1/201812/2/20171
Project212/4/20173/4/201812/9/20171
Project312/16/20173/16/201812/16/20171
Project412/18/20173/18/201812/23/20171
Project512/20/20173/20/201812/23/20171
Project612/21/20173/21/201812/23/20171

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
When you do a pivot, you can add acalculated fiel in Analyse tab. You can name it Weekly count and use a formula
Code:
=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 )
 
Last edited:
Upvote 0
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.

Code:
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.
 
Upvote 0
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.

Code:
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]
 
Upvote 0
Great!!! My computer with power pivot is down so could not test the formula, sorry for the misleading part. Glad it works
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top