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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.