Direct Query working day count date dax

[EloiseJane]

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 calulate(sum of working days(date between function) on an import that works, but my data set is to big for an import so i need to find a way around the limiation for direct query. Any ideas please?! i have an calander ref point already in my model.

thanks

Eloise

[v-eqin-msft · Accepted solution]

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]

@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