formula placement

[Anonymous]

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.

[amitchandak]

@Anonymous , refer if this can help

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

[Anonymous · Accepted 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/

[Greg_Deckler]

Seems right.

Workdays = ([promise date] - [Ship date]) * 1.

[Anonymous]

Thank you @Greg_Deckler , another question, how would i apply the calc to only show the difference for working days (excluding weekends?)

[Greg_Deckler]

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