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 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

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.

2 Replies

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.