Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.
@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
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |