DATEDIFF with Direct Query - Filter out non working days

[Dan70]

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? 

[AnkitBI]

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 Jain

Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.

[Dan70]

Thanks. Is there a way that I can calculate this usign a measure?

[AnkitBI]

Should be possible using Filter and CountRows functions. You can try it out.

[Dan70]

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 Jain

Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.

[Dan70]

Hi

What does this part refer to?

DimDate[FullDateAlternateKey]

[Dan70]

I cant seem to get this to work. It just brings back 1 for each record.

[AnkitBI · Accepted solution]

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)))

[Dan70]

One last question. I want to create an average per day

How would I get a sum of this measure by the posting date? Again this seems to be tricky as its not in a column.

[Dan70]

Thank you. The issue was with my date table.

The starting date did not go far enough back in to the past. I only realised this when i sorted my posting date in decendong order and saw that the latest dates were calculating the measure corrrectly.