Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |