Forum Discussion

vjnvinod's avatar
vjnvinod
Impactful Individual
4 years ago

Measure for Datesbetween

Hi,

 

I need to create a measure to understand the Lead time (total days between 2 dates in the same table excluding Saturday and Sunday)

My table name is Open Orders

 

and below are the 2 date columns

Open Orders'[Open on]
Open Orders [Requested Delivery Date]
 
How to write this measure
i tried Datesbetween dax, have not been sucessful
Need Help
Show and Tell

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    vjnvinod I'm just going to propose the tweak to amitchandak measure:

     

    Work Day =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            CALENDAR ( MAX ( Table[Open on] ), MAX ( Table[Requested Delivery Date] ) ),
            "WorkDay", IF ( WEEKDAY ( [Date], 2 ) < 6, 1, 0 )
        ),
        [WorkDay] = 1
    )
)

     

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

     

    Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

     

     

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    vjnvinod , I think you need workdays

     

     

    Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Open on],Table[Requested Delivery Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

     

    How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA

    • vjnvinod's avatar
      vjnvinod
      Impactful Individual
      4 years ago

      amitchandak 

      was trying to replicate it

      i think something is wrong, after the calendar function, it doesn't read the date column