Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Measure to calculate workdays between id_requesteddeliverydate and id_goodsarrivaldate

Hi Community,

 

i've stumbled across something that i cannot seem to comprehend. 

So we have a facttable which includes these columns (purchaseordernumber, purhcaseorderrownumber, id_requesteddeliverydate,id_goodsarrivaldate):

image.pngDatefact.png

And to this we would like to get a measure that shows the number of working days between id_requesteddeliverydate and id_goodsarrivaldate.

They are each in their own "Dim - Requesteddeliverydate" and "Dim - GoodsArrivalDate" dimensions. Which both includes these columns (with either requested or goodsarrival date in them obviously):

image.png

There is also a "normal" calendar "dim - date" table which is just neutral but includes the same columns as requesteddelivery and goodsarrivaldate. 

Now over to the problem itself, i've been searching here on the forum and online and i just cant get a solution that is working in our case. Is it possible to achieve a measure which calculates the number of working days from the information we have given in these tables or do we need to create a calculated column for this? (pbix file is already massive and we would prefer it to be a measure). Basically i just need to sum the isweekday between these two dates. Or perhaps sum all the isweekday from table goods arrival date up until the id_goodsarrivaldate and then subtract sum of isweekday from table requesteddeliverydate up until the id_requesteddeliverydate and we should get the correct value.


Edit: The Goodsarrivaldate can be before the requesteddate and should then return negative number or 0.

1 REPLY 1
Greg_Deckler
Super User
Super User

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/td-p/367362


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors