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,
trying to figure out how to best model this formula.
I have a simple sales table (however my actual data is very large, but for this example i'm using simple columns).
Order Number | Ship date | promise date | sales $ |
2000 | 2/10/2020 | 2/13/2020 | 5 |
2001 | 2/10/2020 | 2/10/2020 | 10 |
2002 | 2/11/2020 | 1/25/2020 | 5 |
2003 | 2/11/2020 | 2/15/2020 | 10 |
2004 | 2/12/2020 | 2/11/2020 | 5 |
2005 | 2/12/2020 | 2/12/2020 | 10 |
2006 | 2/13/2020 | 2/1/2020 | 15 |
2007 | 2/13/2020 | 2/13/2020 | 20 |
2008 | 2/14/2020 | 2/1/2020 | 25 |
2009 | 2/14/2020 | 3/1/2020 | 25 |
2009 | 2/14/2020 | 2/10/2020 | 30 |
The formual we set up in excel is for on time delivery between promise and actual dates.
My instinct says to add columns and create formulas similar to how i have in my excel file for days late and OTD flag (see below).
Order Number | Ship date | promise date | sales $ | Workdays late | OTD Flag |
2000 | 2/10/2020 | 2/13/2020 | 5 | 3 | On Time |
2001 | 2/10/2020 | 2/10/2020 | 10 | - | On Time |
2002 | 2/11/2020 | 1/25/2020 | 5 | (13) | over 5 days late |
2003 | 2/11/2020 | 2/15/2020 | 10 | 3 | On Time |
2004 | 2/12/2020 | 2/11/2020 | 5 | (3) | 1 to 5 days late |
2005 | 2/12/2020 | 2/12/2020 | 10 | - | On Time |
2006 | 2/13/2020 | 2/1/2020 | 15 | (10) | over 5 days late |
2007 | 2/13/2020 | 2/13/2020 | 20 | - | On Time |
2008 | 2/14/2020 | 2/1/2020 | 25 | (11) | over 5 days late |
2009 | 2/14/2020 | 3/1/2020 | 25 | 10 | On Time |
2009 | 2/14/2020 | 2/10/2020 | 30 | (6) | over 5 days late |
Knowing that i'm not very fluent in PBI, i was wondering if there is another way to either quicken the process time for the user or enhance visuals to do it another way. I am still not sure how the "new table" in modeling works.
Thanks for the feedback.
Solved! Go to Solution.
Thanks guys but I found this link was helpful for what i was trying to do. So far i think it works
https://www.sqlbi.com/articles/counting-working-days-in-dax/
@bearpoz79 , refer if this can help
Thanks guys but I found this link was helpful for what i was trying to do. So far i think it works
https://www.sqlbi.com/articles/counting-working-days-in-dax/
Thank you @Greg_Deckler , another question, how would i apply the calc to only show the difference for working days (excluding weekends?)
https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109
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 |
---|---|
110 | |
98 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |