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.

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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