Forum Discussion

tkk's avatar
tkk
Frequent Visitor
3 years ago
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 ...
Need Help
  • tkk's avatar
    tkk
    3 years ago

    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]))
tkk's avatar
tkk
Frequent Visitor
3 years ago

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]))