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)))
Hi
What does this part refer to?
DimDate[FullDateAlternateKey]
I cant seem to get this to work. It just brings back 1 for each record.
AnkitBISolution Sage
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)))
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.
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.