Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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)))
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.
Thanks. Is there a way that I can calculate this usign a measure?
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.
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.
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.
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)))
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |