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 in that year or future years.

 

Basically, the projects table is something like this:

project IDStart dateEnd date
A20152022
B20182021
C20202020
D20172018

 

and the result should be:

YearNumber of active projects
20183 (A, B and D)
20192 (A and B)
20203 (A,B and C)
20212 (A and B)
20221 (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.
  • 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.

     

     

3 Replies

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

  • -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.