Forum Discussion
Business Time
The error likely comes from how [Date] is used inside _DaysBetweenTable and _WorkingDays. DAX can get confused with column context in these cases, especially when comparing to the bank holiday table.
Try updating _WorkingDays like this:
VAR _WorkingDays =
COUNTROWS (
FILTER (
ADDCOLUMNS (
_DaysBetweenTable,
"IsHoliday",
CALCULATE (
COUNTROWS('Bank Holidays'),
'Bank Holidays'[UK Bank Holidays] = [Date]
)
),
[DayOfWeek] < 6 && [IsHoliday] = 0
)
)
This makes the holiday check more reliable.To troubleshoot, try returning intermediate values (like _WorkingDays) to isolate the issue.
Let me know the exact error message if you're still stuck happy to help further
If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community
Thank you.
Hi ben_hargreaves
Thank you for reaching out microsoft fabric community forum.
Yes, “Transform Data” uses Power Query (M language). Calculating business hours (8 AM–6 PM) between two timestamps while excluding weekends and bank holidays is possible, but a bit complex in M. You’d need a custom function that loops through each datetime, checks if it’s within business hours, and filters out holidays (from your table) and weekends.
It’s doable, but if you’re open to using DAX instead, it might be easier to manage and maintain.
If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community
Thank you.