Filter unrelated fact table based on date selection?

[jd8766]

Hoping somebody has a solution for this.. as I can't figure it out myself.

I have a Sales fact table that contains the details of all sales made within a time period. This is joined onto a SoldUser dimension and a SoldDate dimension.

I also have an unrelated FACT table which shows the details of any days off work an employee has had
FACT.NotWorked

I want to have a slicer on my report for sold date, so I can see sales between '01 aug 2022' and '3 nov 2022' but then I also want it to show me how many days each sold user worked between the 2 selected dates (excluding weekends and any entries in the FACT.NotWorked table)

Is this doable? So we can see in the bottom table, UserKey 40 has not worked 11 days between '01 aug 2022' and '3 nov 2022' so I would need these 11 days to be taken off the total days worked measure.. if that makes sense?

Is this achievable in Power BI? How could I do this?

[PaulDBrown · Accepted solution]

See if this works for you. First the model (I've joined the User table to the leave table)

and the measures

Leave Weekdays  Temp = 
IF(ISINSCOPE('User Table'[SoldUserkey]),
COUNTROWS (
    FILTER (
        ALLSELECTED ( 'Date Table' ),
        'Date Table'[Date] >= MIN ( 'Leave Table'[LeaveStart] )
            && 'Date Table'[Date] <= MAX ( 'Leave Table'[LeaveEnd] )
            && WEEKDAY ( 'Date Table'[Date], 2 ) < 6
    )
)//Counts the number of dates which are >= LeaveStart and <= LeaveEnd and are not on the weekend
)

Final Working days =
VAR _NWD =
    NETWORKDAYS ( MIN ( 'Date Table'[Date] ), MAX ( 'Date Table'[Date] ) ) //Calculates the numbero of working days selected (excludes weekends)
VAR _LWD =
    SUMX ( 'Leave Table', [Leave Weekdays  Temp] ) //Calculates the sum of leave days which are on working days
RETURN
    _NWD - _LWD

Sample PBIX Attached 

[Nishudhan]

Yes it is doable.

You have to use NETWORKDAYS(start_date,end_date,weekends,holidays)

Thanks.

[jd8766]

NETWORKDAYS only brings back the working days between 2 dates.. I also want it to exclude any days not worked based on the 2nd table