cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Dan70 Frequent Visitor
Frequent Visitor

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

 

Tables.JPG

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
AnkitBI Established Member
Established Member

Re: DATEDIFF with Direct Query - Filter out non working days

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)))
10 REPLIES 10
AnkitBI Established Member
Established Member

Re: DATEDIFF with Direct Query - Filter out non working days

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 Frequent Visitor
Frequent Visitor

Re: DATEDIFF with Direct Query - Filter out non working days

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

AnkitBI Established Member
Established Member

Re: DATEDIFF with Direct Query - Filter out non working days

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


Dan70 Frequent Visitor
Frequent Visitor

Re: DATEDIFF with Direct Query - Filter out non working days

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

Networkdays.JPG

 

 

 

 

AnkitBI Established Member
Established Member

Re: DATEDIFF with Direct Query - Filter out non working days

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 Frequent Visitor
Frequent Visitor

Re: DATEDIFF with Direct Query - Filter out non working days

Hi

 

What does this part refer to?

DimDate[FullDateAlternateKey]

 

Dan70 Frequent Visitor
Frequent Visitor

Re: DATEDIFF with Direct Query - Filter out non working days

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

 

Working Days.JPG

AnkitBI Established Member
Established Member

Re: DATEDIFF with Direct Query - Filter out non working days

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 Frequent Visitor
Frequent Visitor

Re: DATEDIFF with Direct Query - Filter out non working days

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.

 

Working Days.JPG

 

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 129 members 1,868 guests
Please welcome our newest community members: