How to get the Value of related table where Date +1 matches?

[RR-Ak-Lex]

I need to calculate the next possible working day. 

So when its Friday, it should calculate Monday. If its a holiday, (for example Tuesday) and today is Monday, it should return Wednesday. 

What I have calculated are 2 Columns that display the date before and after the holiday date on every day there is a holiday.

"DatumVorher" The first day of Holiday/Weekend

"DatumNachher" The last day of Holiday/Weekend

My Date-Column is Linked to the "Date" on my Date Table. 

So when I want to match the current day, wether the next day would be a holiday or weekend, I use this Dax, which works:

DATEADD('Measurement Job List PEZ Calculation Termination'[Termination Date], 1, DAY) = RELATED(Betriebskalender[DatumVorher])

But wrapping this into an IF() statment, I cannot seem to understand how to get the Value of 

RELATED(Betriebskalender[DatumVorher])

Related to the next day of the Date-Column.

This way I could get the first and last day and calculate the difference, add that to the Date-Column and that would be my solution.

[Anonymous · Accepted solution]

HI @RR-Ak-Lex,

You can take a look at the following measure formula to get the difference betwen the current and next workday:

formual =
VAR holidayList =
    ALLSELECTED ( Holiday[Date] )
VAR currDate =
    MAX ( Table[Date] )
VAR nextDate =
    MINX (
        FILTER (
            EXCEPT ( 'Calendar', holidayList ),
            [Date] > currDate
                && WEEKDAY ( [Date], 2 ) < 6
        ),
        [Date]
    )
RETURN
    DATEDIFF ( currDate, nextDate, DAY )

Regards,

Xiaoxin Sheng

[RR-Ak-Lex]

This looks very nice!
I only have an issue with the EXCEPT() Function you used. What exactly is 'Calendar' referring to?
I only have a "Date" Table and no "Holidays" and no "Calendar" Table. 

[Anonymous]

HI @RR-Ak-Lex,

In fact, it is a placeholder and you can use your date calendar table to replace this to get corresponding results.

Regards,

Xiaoxin Sheng

[PC2790]

Hey @RR-Ak-Lex ,

Have you considered using the recently introduced NETWORKDAYS dax function.

You just have to give the start date and end date and it will calculate the working days for you excluding the weekends.

There is also a parameter to include the list of holidays to get them excluded from the count of days.

You can take a look and see if it works out for your requirement.

https://www.youtube.com/watch?v=hL4wCObZ52w