Next inspection date excluding weekends

[tkk]

Hi all,

I have a table that contains inspection data with inspections that occur on either a weekly, or daily basis. In my query editor I attempt to display the next inspection date based off of a 'frequency' column that contains either "Daily" or "Weekly".  My current formula is as follows: 

"Next Inspection Day", each if [Count.frequency] = "Weekly" then Date.AddDays([Count.entry_timestamp],7) else Date.AddDays([Count.entry_timestamp],1))

The issue I am facing is that this results in the next inspection day showing as weekends for some inspections depending on the date of their entry timestamp. This is the result for my visual showing completion status, and next inspection day. 

The weekly checks aren't affected due to my equation adding 7 days to the entry timestamp and how any inspections will be completed during the work week. I imagine the solution will have to correct that as well as the daily issue but I haven't been able to figure anything out. 

Also please note that the "Week day" column in the image is the expected completion day, not the day it was actually completed.

Any help is appreciated

Thanks, 

tkk

[tkk · Accepted solution]

I ended up figuring it out. I was able to use the same equation that I listed above that adds a flat 7 or a 1 depending on the frequency column and wrote that date to a column called "expected inspection day". I then created a new column called "next inspection day" and used this equation to correct the potential saturday and sunday expected inspection days: 

Next Inspection Day = IF(WEEKDAY('Table'[Expected Inspection Day].[Date])=1, DATEADD('Table'[Expected Inspection Day].[Date],1,DAY), IF(WEEKDAY('Table'[Expected Inspection Day].[Date])=7, DATEADD('Table'[Expected Inspection Day].[Date],2,DAY), 'Table'[Expected Inspection Day].[Date]))

[Padycosmos]

Hope this video helps:

https://www.youtube.com/watch?v=e9rRfWJy7Co

[tkk]

This had a different set up than what I am making use of, this video was subtracting days to find a start date while I am adding days to find the next occurence date. It did however give me an idea to solve my problem, so thank you for sharing!