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.
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):
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):
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.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/td-p/367362
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |