Forum Discussion

-JMG-'s avatar
-JMG-
New Member
2 years ago
Solved

Active projects per year

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...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

-JMG-'s avatar
-JMG-
New Member
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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.