calculate number of working days between 2 dates

[Ilrale]

Hi all, 

I'm new in Dax and Power BI. 

I try to compare the due date of an invoice with a custom date the users will manually select. 

It works with to today date in automatique "int(DATE(YEAR(NOW());MONTH(NOW());DAY(NOW())) - 'DATE_DUDDAT')" 

Someone having an idea ? 

[v-jiascu-msft · Accepted solution]

Hi @Ilrale,

Firstly, you need a date table that is with no relationship with your current table. Because the dates will be filtered out if you use the dates in the invoice table or in the table having relationship with invoice table. You can try it out in this file.

1. A new date table if you don't have one.

Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2018, 2, 28 ) ),
    "IsWeekend", IF ( WEEKDAY ( [Date], 2 ) IN { 6, 7 }, 1, 0 )
)

2. Create a measure.

WorkdaysCount =
DATEDIFF ( MIN ( 'Table'[Date] ), MIN ( 'Calendar'[Date] ), DAY ) + 1
    - SUMX (
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] >= MIN ( 'Table'[Date] )
                && 'Calendar'[Date] <= MIN ( 'Calendar'[Date] )
        ),
        'Calendar'[IsWeekend]
    )

Best Regards,

Dale