Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |