How to count rows filtered by unrelated table

[Anonymous]

Hi there!

I have two unrelated tables:
1. Calendar table, single date
2. Project table, two dates: start date, end date.

I use a date slicer, and I want to count how many days the project period covers during that time frame.

I tried many measures, such as:

Days = 
CALCULATE(
DISTINCTCOUNT('Calendar'[Date]),
        'Job'[Start] <= MAX('Calendar'[Date]),
        'Job'[End] >= MIN('Calendar'[Date])
    
)

In the example, it should be 4 (because 4 days in Feb 2020) but I only get 29, it counts the whole month. I keep searching everywhere for a solution but I can't find any....

Please help me! 🙂

[Icey · Accepted solution]

Hi @Anonymous ,

How about this?

Days =
COUNTROWS (
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Date] >= MAX ( Job[Start] )
            && 'Calendar'[Date] <= MAX ( Job[End] )
    )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I fixed the days calculation like this:

I created two extra measures:

JobMin = 
VAR JobMin = CALCULATE(
    MINX('Job',[Start]),
        'Job'[Start] <= MAX('Calendar'[Date]),
        'Job'[End] >= MIN('Calendar'[Date])
)

RETURN 

if( 
NOT ISBLANK(JobMin) && JobMin <MIN('Calendar'[Date]), 
    MIN('Calendar'[Date]), 
    JobMin
)

and 

JobMax = 
VAR JobMax = CALCULATE(
    MAXX('Job',[End]),
        'Job'[Start] <= MAX('Calendar'[Date]),
        'Job'[End] >= MIN('Calendar'[Date])
)

RETURN 

if( 
NOT ISBLANK(JobMax) && JobMax >MAX('Calendar'[Date]), 
    MAX('Calendar'[Date]), 
    JobMAX
)

And so I fixed the days calculation like this:

Days = 

Var MinJob = [JobMin]
VAR MaxJob = [JobMax]

Var MaxDate = MAX('Calendar'[Date])
var MinDate = MIN('Calendar'[Date])

RETURN 

CALCULATE(
    SUMX('Job',DATEDIFF(if('Job'[Start]<MinJob,MinJob,'Job'[Start]), if('Job'[End]>MaxJob, MaxJob, 'Job'[End]), DAY) +1),
    'Job'[Start] <= MaxDate,
    'Job'[End] >= MinDate
    
)

Result:

(ps: current utilization = quantity x days)

Still checking all numbers...