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

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

Hi

 

What does this part refer to?

DimDate[FullDateAlternateKey]

 

  • Dan70's avatar
    Dan70
    Frequent Visitor
    6 years ago

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

     

    • 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

        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.