topic Re: Help with Measures that Counts How Many Projects Are Starting in Various Time Frames in DAX Commands and Tips

Help with Measures that Counts How Many Projects Are Starting in Various Time Frames

chicagolarsons — Mon, 05 Aug 2024 19:09:45 GMT

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!

ConStart+30 = IF(CALCULATE(COUNTROWS('Project Tasks'), FILTER('Project Tasks', [Task Name]="Mobilization" && [Start]>=TODAY()&&[Start]<=TODAY()+30))=BLANK(),0,CALCULATE(COUNTROWS('Project Tasks'), FILTER('Project Tasks', [Task Name]="Mobilization" && [Start]>=TODAY()&&[Start]<=TODAY()+30)))

ConStart+60 = IF(CALCULATE(COUNTROWS('Project Tasks'), FILTER('Project Tasks', [Task Name]="Mobilization" && [Start]>=TODAY()+30 && [Start]<=TODAY()+60))=BLANK(),0,CALCULATE(COUNTROWS('Project Tasks'), FILTER('Project Tasks', [Task Name]="Mobilization" && [Start]>=TODAY()+31 && [Start]<=TODAY()+60)))

ConStart+90 = if(CALCULATE(COUNTROWS('Project Tasks'), FILTER('Project Tasks', [Task Name]="Mobilization" && [Start]>=TODAY()+61 && [Start] <=TODAY()+90))=BLANK(),0,CALCULATE(COUNTROWS('Project Tasks'), FILTER('Project Tasks', [Task Name]="Mobilization" && [Start]>=TODAY()+61 && [Start] <=TODAY()+90)))

ConStart+120 = if(CALCULATE(COUNTROWS('Project Tasks'), FILTER('Project Tasks', [Task Name]="Mobilization" && [Start]>=TODAY()+91 && [Start] <=TODAY()+120))=BLANK(),0,CALCULATE(COUNTROWS('Project Tasks'), FILTER('Project Tasks', [Task Name]="Mobilization" && [Start]>=TODAY()+91 && [Start] <=TODAY()+120)))

Thank you for looking and trying to help me!

Kirstin

Re: Help with Measures that Counts How Many Projects Are Starting in Various Time Frames

bhanu_gautam — Tue, 06 Aug 2024 05:41:44 GMT

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
)
)
)

Re: Help with Measures that Counts How Many Projects Are Starting in Various Time Frames

Anonymous — Wed, 07 Aug 2024 07:30:08 GMT

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 Team

If 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