Forum Discussion

fjjpeeters1976's avatar
fjjpeeters1976
Helper III
3 years ago

Distinctcount/calculate with two dates

Hi all,

 

I have a list of projects that have a planned L3 date column and an actual L3 date column. The actual L3 date is linked as active relationship to a timetable, the planned L3 date is inactive. I want to count the projects that where planned to be done in 2023 but already have been done in 2022. 
I would need some nested Calculate statement, but I do not get it right. I tried the following formular (among others), but that does not seem to do the trick

 

Projects executed early =
CALCULATE (
    (
        CALCULATE (
            DISTINCTCOUNT ( CapexTiming[initiative #] ),
            USERELATIONSHIP ( TimeTable[Date], CapexTiming[L3 planned date] ),
            TimeTable[Year] = "2023"
        )
    ),
    KEEPFILTERS ( TimeTable[Year] = "2022" )
)

2 Replies

  • Can you avoid using the inactive relationship?

     

    Projects executed early =
            CALCULATE (
                DISTINCTCOUNT ( CapexTiming[initiative #] ),
                YEAR( CapexTiming[L3 planned date] ) = 2023,
                TimeTable[Year] = "2022"
            )

  • Another idea:

    Rather than having one date table with active/inactive relationships, have two date tables (one for actual dates and one for planned dates).