Forum Discussion
Help with Measures that Counts How Many Projects Are Starting in Various Time Frames
Hi, I'm hoping someone here can help me fix some measures I've made. I have set up four measures to count how many construction projects start in various time frames: 30, 60, 90, and 120 days. The measure is written exactly the same way for all 4, with just variance in the dates. I am getting a count returned, but it is not correct. The screenshot below shows the Construction Start Dates, and also the first measure. The measure is intended to count how many instances of the task name "mobilization" occur between the specified date range.
As you can see, between today and today +30 days (so, before ~9/5/2024) there are 2 projects scheduled to start, but the measure returns a count of 1.
For Construction Starts between 30 and 60 days (~9/6 to ~10/6) which is correct.
For Construction Starts between 60 and 90 days (~10/7 to ~11/6), it counts 2, which is correct.
For Construction Starts between 90-120 days (~11/8 to ~12/9), it counts 1, but there are 2 projects scheduled to start in that time period.
Here is the syntax I've used for each of the measures. I'm pretty sure this is a syntax issue, but haven't been able to figure out what I'm doing wrong. Any help is appreciated!
Thank you for looking and trying to help me!
Kirstin
2 Replies
- bhanu_gautam
Super User
chicagolarsons , Try with updated measures
ConStart+30 =
IF(
ISBLANK(
CALCULATE(
COUNTROWS('Project Tasks'),
FILTER('Project Tasks',
[Task Name] = "Mobilization" &&
[Start] >= TODAY() &&
[Start] <= TODAY() + 30
)
)
),
0,
CALCULATE(
COUNTROWS('Project Tasks'),
FILTER('Project Tasks',
[Task Name] = "Mobilization" &&
[Start] >= TODAY() &&
[Start] <= TODAY() + 30
)
)
)ConStart+60 =
IF(
ISBLANK(
CALCULATE(
COUNTROWS('Project Tasks'),
FILTER('Project Tasks',
[Task Name] = "Mobilization" &&
[Start] >= TODAY() + 31 &&
[Start] <= TODAY() + 60
)
)
),
0,
CALCULATE(
COUNTROWS('Project Tasks'),
FILTER('Project Tasks',
[Task Name] = "Mobilization" &&
[Start] >= TODAY() + 31 &&
[Start] <= TODAY() + 60
)
)
)ConStart+90 =
IF(
ISBLANK(
CALCULATE(
COUNTROWS('Project Tasks'),
FILTER('Project Tasks',
[Task Name] = "Mobilization" &&
[Start] >= TODAY() + 61 &&
[Start] <= TODAY() + 90
)
)
),
0,
CALCULATE(
COUNTROWS('Project Tasks'),
FILTER('Project Tasks',
[Task Name] = "Mobilization" &&
[Start] >= TODAY() + 61 &&
[Start] <= TODAY() + 90
)
)
)ConStart+120 =
IF(
ISBLANK(
CALCULATE(
COUNTROWS('Project Tasks'),
FILTER('Project Tasks',
[Task Name] = "Mobilization" &&
[Start] >= TODAY() + 91 &&
[Start] <= TODAY() + 120
)
)
),
0,
CALCULATE(
COUNTROWS('Project Tasks'),
FILTER('Project Tasks',
[Task Name] = "Mobilization" &&
[Start] >= TODAY() + 91 &&
[Start] <= TODAY() + 120
)
)
) Hi chicagolarsons ,
I'm assuming the return value of Today() is 2024-08-07. Then the only eligible record from 2024-08-07 to 2024-09-06 as described above is this one:
Or did I misunderstand your question?
Using [ConStart+30] as an example, I usually write it like this:
ConStart+30 = CALCULATE ( COUNTROWS ( 'Project Tasks' ), FILTER ( ALL ( 'Project Tasks' ), 'Project Tasks'[Task Name] = "Mobilization" && 'Project Tasks'[Start] >= TODAY () && 'Project Tasks'[Start] <= TODAY () + 30 ) )Or:
ConStart+30 = SUMX ( FILTER ( ALL('Project Tasks'), 'Project Tasks'[Task Name] = "Mobilization" ), CALCULATE ( COUNTROWS ( 'Project Tasks' ), 'Project Tasks'[Start] >= TODAY () && 'Project Tasks'[Start] <= TODAY () + 30 ) )Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
