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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ConnorH
Microsoft Employee
Microsoft Employee

Date Management

Hey all, 

 

I have been trying to figure out a way to solve this date issue I am running into with my data mess. So an example is we have two years worth of calender data. In one day there should not be overlapping time frames. 

Example:

SubjectStart DateStart TimeEnd DateEnd Time
Tentatively available7/21/20199:00 AM 7/21/201910:30 AM
call sheet  7/21/201911:00 AM 7/21/20194:00 PM
Assesories 7/21/20193:00 PM 7/21/20195:00 PM
Maintanance 7/21/20195:00 PM 7/21/2019 5:30 PM

 

So this could be an example of one days date data we pull. Now I am going to show a table of what my goal is:

 

SubjectStart DateStart TimeEnd DateEnd Date
Tentatively available7/21/20199:00 AM 7/21/201910:30 AM
call sheet  7/21/201911:00 AM 7/21/20194:00 PM
Assesories 7/21/20194:00 PM 7/21/20195:00 PM
Maintanance 7/21/20195:00 PM 7/21/2019 5:30 PM

 

So, basically there can not be overlapping time frames for anyday of data. Also, there is a rank for these subjects. Meaning if a call sheet has a time interference with another subject, lets say assesories due to this example, then the call sheet holds the interfered time within its start time and end time. 

 

Ranking of subjects: 1. Call Sheet 2.Assesories 3.Maintanance 4.Tentatively available.

 

I have previously posted this in the DAX forum and was told this was a power query post. Would appreciate any direction, 

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

Since you posted in two places, I had to take a shot at this one.  Turned out to be a little tricky, and solved more easily in DAX IMO.  I put your data into a table called "Schedule" and added these two calculated columns to get your desired result.  If it works for you and you want explanation of how it works, please let me know.

 

Priority = SWITCH(Schedule[Subject], "call sheet",1,"Assesories",2,"Maintanance",3,"Tentatively available",4)
 
Adj Start Time =
var starttime = Schedule[Start Time]
var endtime = Schedule[End Time]
var currentpriority = Schedule[Priority]
var conflictTF = CALCULATE(COUNTROWS(Schedule), All(Schedule), Schedule[Priority]<currentpriority, Schedule[Start Time]<endtime, Schedule[End Time]>starttime)>0 // returns True or False if a higher priority meeting conflicts with this meeting
return if(conflictTF, CALCULATE(MAX(Schedule[End Time]), All(Schedule), Schedule[Priority]<currentpriority, Schedule[Start Time]<endtime, Schedule[End Time]>starttime),starttime) // if a conflict, finds the max end time of any conflicting higher priority meetings

 

 

schedule.png





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
mahoneypat
Microsoft Employee
Microsoft Employee

Since you posted in two places, I had to take a shot at this one.  Turned out to be a little tricky, and solved more easily in DAX IMO.  I put your data into a table called "Schedule" and added these two calculated columns to get your desired result.  If it works for you and you want explanation of how it works, please let me know.

 

Priority = SWITCH(Schedule[Subject], "call sheet",1,"Assesories",2,"Maintanance",3,"Tentatively available",4)
 
Adj Start Time =
var starttime = Schedule[Start Time]
var endtime = Schedule[End Time]
var currentpriority = Schedule[Priority]
var conflictTF = CALCULATE(COUNTROWS(Schedule), All(Schedule), Schedule[Priority]<currentpriority, Schedule[Start Time]<endtime, Schedule[End Time]>starttime)>0 // returns True or False if a higher priority meeting conflicts with this meeting
return if(conflictTF, CALCULATE(MAX(Schedule[End Time]), All(Schedule), Schedule[Priority]<currentpriority, Schedule[Start Time]<endtime, Schedule[End Time]>starttime),starttime) // if a conflict, finds the max end time of any conflicting higher priority meetings

 

 

schedule.png





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


For some reason it is not returning the right start time. Could this be because this formula searches a whole table rather than by day? I am geting 5:30pm Start time for every value.

Please confirm you are using the expression in calculated columns and not a measure (expression would need to be adjusted for measure).  Did it work when you apply it to the sample data your provided?  Are there differences with your actual data?  Did you have to modify the expression? 

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hi Pat,

 

Just went through and yes it does work on the sample data but not my actual data. I should have put out a more rounded example. Thank you so much for your time. I have linked to an Excel file which holds an almost itentical match to my actual data. I think an issue was I have hundereds of different days but from what I understood from the solution is it searches the whole table and adjusts start dates instead of adjusting day by day. Not really sure how to do it.   Dropbox Excel File 

 

Thank you for the help!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors