Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tkk
Frequent Visitor

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 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. 

CurrentCompletionTable.jpg

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

1 ACCEPTED SOLUTION
tkk
Frequent Visitor

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

View solution in original post

3 REPLIES 3
tkk
Frequent Visitor

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
Solution Sage
Solution Sage

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors