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:
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:
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
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 Name | Start Date | End Date | End of Week | Weekly Count |
Project1 | 12/1/2017 | 3/1/2018 | 12/2/2017 | 1 |
Project2 | 12/4/2017 | 3/4/2018 | 12/9/2017 | 1 |
Project3 | 12/16/2017 | 3/16/2018 | 12/16/2017 | 1 |
Project4 | 12/18/2017 | 3/18/2018 | 12/23/2017 | 1 |
Project5 | 12/20/2017 | 3/20/2018 | 12/23/2017 | 1 |
Project6 | 12/21/2017 | 3/21/2018 | 12/23/2017 | 1 |
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>