Forum Discussion
Solved
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 workig...
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)))
AnkitBI
Solution Sage
Solution SageShould be possible using Filter and CountRows functions. You can try it out.
I dont quite understand how the COUNTROWS function would work?
I have been looking at anoter post that uses the measure below. Could this be modified to filter on my [is workign day] column.
NetWorkDays =
VAR Calendar1 = CALENDAR(MAX('Whse Shipment Header - Birchwood'[Shipment Date]),MAX('Whse Shipment Header - Birchwood'[Posting Date]))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])
- AnkitBI
Hi - Try something like below. I am using Import but shall work with Direct Query.
Working Days = COUNTROWS(Filter(DimDate,AND(AND(DimDate[FullDateAlternateKey] <= max(WHSE[Posting Date]),DimDate[FullDateAlternateKey] >= max(WHSE[Shipment Date])),DimDate[Is Working Day] = 1)))
Thanks
Ankit JainDo Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.