Active projects per year

[-JMG-]

Hi!

I'm scratching my head with a measure to count projects that are active in a year or a period.

A project is active in a given year if it has started in that year or previous years and has ended in that year or future years.

Basically, the projects table is something like this:

project ID	Start date	End date
A	2015	2022
B	2018	2021
C	2020	2020
D	2017	2018

and the result should be:

Year	Number of active projects
2018	3 (A, B and D)
2019	2 (A and B)
2020	3 (A,B and C)
2021	2 (A and B)
2022	1 (A)

I've created a calendarauto table and linked it to both start date and end date of the projects table.

The DAX code I've used, which is obviously wrong, is:

Number of active projects = calculate(DISTINCTCOUNT('projects'[project ID]),FILTER('projects','projects'[start date] <= max(Dates[Date]) && 'projects'[end date] >= MIN(Dates[Date])))

I do this all the time with excel (countifs) and I though the same strategy would apply, but there is something I'm not doing correctly.

Any help would be appreciated.

[-JMG-]

Thanks

Besides I've written a bit of the code wrong (the last >=MAX shoud be >=MIN), the trick was to disable the link between the date table and the project table. Without those relations, it works.

Why is that?

[Anonymous · Accepted solution]

Hi @-JMG- ,

Based on your statement, I think you could try comparing by year rather than date. This would then eliminate the need to use MIN() and MAX() questions.

Date Table:

Date = ADDCOLUMNS(CALENDAR(DATE(2018,01,01),DATE(2023,12,31)),"Year",YEAR([Date]))

Measure:

Number of active projects =
CALCULATE (
    DISTINCTCOUNT ( 'projects'[project ID] ),
    FILTER (
        'projects',
        'projects'[start date] <= MAX ( 'Date'[Year] )
            && 'projects'[end date] >= MIN ( 'Date'[Year] )
    )
)

Result is as below. If [Start Date] and [End Date] are both date type data, you can use YEAR() function to get year.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Abhinav054]

Hii, Actually the code looks fine, But Dax is Case-Sensitive. Once try this code,

Number of active projects = CALCULATE(
                                                         DISTINCTCOUNT('projects'[project ID]),
                                                                                                              FILTER(
                                                                                                                        'projects',
                                                                                                     'projects'[start date] <= MAX(Dates[Date]) &&
                                                                                                      'projects'[end date] >= MAX(Dates[Date])
                                 )
)