Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Datediff - Ignore weekends

Hi All

 

I have a very simple visulisation board  for vacations.   Just for some Engineers (source is Asana)

 

I have a Out of Office Start and an out of office end

 

OOU=Out of Office

 

Holdiay start and holiday end.

 

How could I use a DAX to do a date diff but ignore weekend dates as a day i.e Sat/Sun are not totalled as they are non working?

 

Also in below where it shows as ZERO as someone is TAKING a single day off. In reality its 1x day? How would you show this?

 

No one asked for a days total; I just thought I would show it as a bit of value add.

 

It looks like so in Fig 1

 

Fig 1

 

 

 

Apprecitae any advice.

 

Jimmy

 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    Below is my table:

    The following DAX might work for you:

    Holidy_num = WEEKDAY('Table'[OOU Start],2)

holiday = 
IF('Table'[Holidy_num]<>6 && 'Table'[Holidy_num]<>7 , 
DATEDIFF('Table'[OOU Start],'Table'[OOU End],DAY),
BLANK())

    You can create a holiday_num to examine holiday

    The final output is shown in the following figure:

    Best Regards,

    Xianda Tang

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Anonymous   Big thanks for the advice :   See below how it came out:

     

    The last 2x columns are the ones you suggested- notice  the holidays taken is ZERO (You called it Holiday)

     

     

     

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    hi, Anonymous 

    try below for new column 

     

    column =
NETWORKDAYS(table[oou start],table[oou end],1) here 1 for holiday(sat, sun)
                                                    2 for holiday(sun, mon)

     

    for measure

    measure = 
 NETWORKDAYS(max(tablename[oou star]),max(tablename[oou end]),1)