Working Days between two dates

[cottrera]

Hi

I have a dates table

And a facts table  

Reference Number	Date Logged	Target Days	Target Date
24785	01/01/2019	5	DAX Required
34375	01/01/2019	1	DAX Required
61322	01/01/2019	15	DAX Required
53067	01/01/2019	15	DAX Required
46719	02/01/2019	15	DAX Required
36768	02/01/2019	1	DAX Required
58500	02/01/2019	60	DAX Required
61417	03/01/2019	60	DAX Required
47117	03/01/2019	5	DAX Required

I would like a DAXfunction that adds the Taget Days to the Date Logged but calculates this in working days.

For example the fist line of the facts table , the logged date is 01/01/2019 which is a Friday so by adding due days 5 would make the due date 06/01/2019.  This is not what I require as I need to include only working days.  The correct due date would be 08/01/2019.

I have a date table which has a column called is_workignday.

thank you

RIchard

Richard

[cottrera]

Thank you for your quick reponse you dax functions work fine 😀

[Anonymous · Accepted solution]

@cottrera , you can do this by adding a Calculated Column to your facts table:

Target Date = 
    MAXX(
        TOPN(
            'facts'[Target Days]
            , FILTER(
                'Dates'
                , 'Dates'[Date] >= 'facts'[Date Logged]
                && 'Dates'[is_workday] = 1
            )
        )
        ,'Dates'[Date]
    )

If you want to create a measure first to see how it will look, teh measure syntax is slightly different:

Target Date (measure) = 
    VAR vLogged = SELECTEDVALUE('facts'[Date Logged])
    VAR VDays = SELECTEDVALUE('facts'[Target Days])
    RETURN
        MAXX(
            TOPN(
                vDays
                , FILTER(
                    'Dates'
                    , 'Dates'[Date] >= vLogged
                    && 'Dates'[is_workday] = 1
                )
            )
            ,'Dates'[Date]
        )