Forum Discussion

Dan70's avatar
Dan70
Frequent Visitor
6 years ago
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...
Need Help
  • AnkitBI's avatar
    AnkitBI
    6 years ago

    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's avatar
AnkitBI
Icon for Solution Sage rankSolution Sage
6 years ago

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's avatar
Dan70
Frequent Visitor
6 years ago

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.