Forum Discussion
Solved
Next inspection date excluding weekends
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 ...
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]))
Hope this video helps:
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!