Forum Discussion
DATEDIFF with Direct Query - Filter out non working days
I want to use the date diff function to show the number of days it takes to pack an order in the warehouse. Warehouse [Shipment date] - Warehouse [Posting date] .
Im trying to filter out non workign days. I have created a date table to show dates that are workign days.
Ive looked at lots of methods posted on this forum but I canot seem to make any of them work with my data.
When i try and create a new column in my warehosue table the relationship with the date table does not seen to work. Is this a limitation of usign direct query?
Do you have a Relationship b/w two tables. Try without it or add ALL in Date. If still not work, share PBIX file.
COUNTROWS(Filter(all(DimDate),AND(AND(DimDate[FullDateAlternateKey] <= max(WHSE[Posting Date]),DimDate[FullDateAlternateKey] >= max(WHSE[Shipment Date])),DimDate[Is Working Day] = 1)))
10 Replies
Are you trying to Calculate Column in warehouse using data in Date Table. Then it's a limitation of Direct Query that you can't refer columns from other tables. Please check Limitations in calculated columns at below.
.https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about
Thanks
Ankit JainDo Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.
