I need to calculate the number of days between two date fields EXCLUDING weekends. I have tried a bunch of times but can't seem to get it correct. It is a simple single data table with an "Order Date" field and "Invoice Date" field. I just want to know the number of work days between them. I know I can subtract to get total days but I need to take out the weekend days for it to be accurate for my purposes.
I do not want to use or import a separate Calendar Table to do this, since I have both required date fields in the original data table. Excel can do this quite easily but Power BI should have a simpler way to do it. Am I missing something?
i.e. An Order Date = 12/6/17 & Invoice Date = 1/9/2018 - is 34 days, but is really "24" week days.....
could you create a table (or add to a calendar table) a flag if it is a working day? for example, If M-F then 1, otherwise Sat and Sun would be 0? Then, do a calculate with the sum set to that workday flag, and a filter to the date is greater than or equal to the Order date and another filter that is less than or equal to the invoice date?
These should at least get you close to what you're looking for. Be sure to create measure of this. You may need to create another date column or something that can have a relationship connected to it, but I could be wrong in that. Like you, I haven't been working with Power BI for more than a few months. With that said, someone more experienced may have something better to add to this, or something easier.