Forum Discussion
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
- whitchResolver I
Can you avoid using the inactive relationship?
Projects executed early =
CALCULATE (
DISTINCTCOUNT ( CapexTiming[initiative #] ),
YEAR( CapexTiming[L3 planned date] ) = 2023,
TimeTable[Year] = "2022"
) - whitchResolver I
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).