Forum Discussion

EloiseJane's avatar
EloiseJane
Frequent Visitor
4 years ago
Solved

Direct Query working day count date dax

Hi   HELP...   I am trying to do a date between count with some direct query data to estabish the amount of working days between application received and application validated.   I have tried c...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi EloiseJane ,

     

    Since this: i have an calander ref point already in my model. Please try:

    Measure =
VAR _t =
    FILTER (
        ADDCOLUMNS ( 'Calendar', "WeekDay", WEEKDAY ( [Date], 2 ) ),
        [Date] >= MAX ( 'DateTable'[application received] )
            && [Date] <= MAX ( 'DateTable'[application validated] )
            && [WeekDay] IN { 1, 2, 3, 4, 5 }
    )
RETURN
    COUNTROWS ( _t )

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

amitchandak's avatar
amitchandak
Super User
4 years ago

EloiseJane , You can try a measure like

Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Min(Table[Start Date]),Max(Table[End Date])),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

 

or

 

Work Day = sumx(values(Table[ID]), COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Min(Table[Start Date]),Max(Table[End Date])),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1)) )

 

 

refer

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