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
andersona1983
Helper I
Helper I

Checking Time Values

Hello All,

 

I have attempted to write a few columns, that check a previous time value against a current time value. 

 

I have a power query, that sorts my rows on import from Excel. Theres a conditional column, that returns a value based on another, any value of 1 is then filtered to show in the remaining table. The table is then sorted and comes up with the appropriate layout (Sorted by Office, Client, Appt Date, Appt Start Time). This has been confirmed to be correct. At this point is where I come with issue. No matter what formula I have attempted I can't get the values to deduct from each other. 

 

These are the new columns done via DAX:

 

1. Appt Start Time = format('AA-QC'[Appointment Start Datetime],"HH:MM:SS")

2. Appt End Time = format('AA-QC'[Appointment End Datetime],"HH:MM:SS")

3. Prev Start Time = format(CALCULATE(MAX('AA-QC'[Appt Start Time]),FILTER('AA-QC','AA-QC'[Index -1]=EARLIER('AA-QC'[Index])-1)),"HH:MM:SS")​

4. Prev End Time = format(CALCULATE(MAX('AA-QC'[Appt End Time]),FILTER('AA-QC','AA-QC'[Index -1]=EARLIER('AA-QC'[Index])-1)),"HH:MM:SS")

 

This is the column I have attempted to create. I have verified that appt starts/ends (APPT and PREV) and both show the correct format, and information.

 

Overlap = if( 'AA-QC'[Appt Start Time] < 'AA-QC'[Prev End Time] , "Overlap" , if( 'AA-QC'[Appt Start Time] = 'AA-QC'[Prev End Time], "Overlap" , ""))

 

Some great tutorials to help teach on this would be appreciated!

 

Thanks!

My Data Table:

Client Office NameAppt. StatusStaff NameAppt. DateSubject LineAppointment Start DatetimeAppointment End DatetimeIs RenderedService CodeService CategoryClient Name
CENTRALACTIVEHer, Joh8/3/2020XXXX8/3/2020 11:008/3/2020 14:00Yes

DIR

AAdoe, John
CENTRALACTIVEGar, Tam8/3/2020XXXX8/3/2020 14:008/3/2020 16:00YesDIRAAdoe, John
CENTRALACTIVEMar, Mar8/3/2020XXXX8/3/2020 16:008/3/2020 18:00YesDIRAAdoe, John
CENTRALACTIVEOls, And8/3/2020XXXX8/3/2020 16:308/3/2020 17:00YesDIRAAdoe, John
CENTRALACTIVEHer, Joh8/3/2020XXXX8/4/2020 11:008/4/2020 14:00YesDIRAAdoe, John
CENTRALACTIVEGar, Tam8/3/2020XXXX8/4/2020 14:008/4/2020 16:00YesDIRAAdoe, John
CENTRALACTIVEMar, Mar8/3/2020XXXX8/4/2020 16:008/4/2020 18:00YesDIRAAdoe, John
CENTRALACTIVEHer, Joh8/3/2020XXXX8/5/2020 11:008/5/2020 14:00YesDIRAAdoe, John
CENTRALACTIVEGar, Tam8/3/2020XXXX8/5/2020 14:008/5/2020 16:00YesDIRAAdoe, John
CENTRALACTIVEMar, Mar8/3/2020XXXX8/5/2020 16:008/5/2020 18:00YesDIRAAdoe, John
CENTRALACTIVEOls, And8/3/2020XXXX8/5/2020 16:308/5/2020 17:00YesDIRAAdoe, John
CENTRALACTIVEHer, Joh8/3/2020XXXX8/6/2020 11:008/6/2020 14:00YesDIRAAdoe, John
CENTRALACTIVEGar, Tam8/3/2020XXXX8/6/2020 14:008/6/2020 16:00YesDIRAAdoe, John
CENTRALACTIVEMar, Mar8/3/2020XXXX8/6/2020 16:008/6/2020 18:00YesDIRAAdoe, John
CENTRALACTIVEOls, And8/3/2020XXXX8/6/2020 16:008/6/2020 16:30YesDIRAAdoe, John
1 ACCEPTED SOLUTION

Hi @andersona1983,

 

Feel like I am a little late to the party, but given your data I think the following will provide the 'Prev' columns you need
@andersona1983 - Ammended to include overlap column and removed formatting as the comparison needs to compare the actual dates and not just the time strings. You can format the columns in another column if required

 

Prev End Time = 
    var client = [Client Name]
    var index = [Index]
return
    CALCULATE(MIN('Table'[Appointment End Datetime]), FILTER(ALL('Table'), 'Table'[Client Name] = client && 'Table'[Index] = index -1))

Prev Start Time = 
    var client = [Client Name]
    var index = [Index]
return
    (CALCULATE(MIN('Table'[Appointment Start Datetime]), FILTER(ALL('Table'), 'Table'[Client Name] = client && 'Table'[Index] = index -1)))

Overlap = 
if( 'Table'[Appointment Start Datetime]< 'Table'[Prev End Time] || 'Table'[Appointment Start Datetime] = 'Table'[Prev End Time], "Overlap" , "")

 

 

Hope this Helps,

Richard


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

11 REPLIES 11
Greg_Deckler
Super User
Super User

@andersona1983 - Not entirely sure I understand but if you are trying to compare rows in DAX, See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hello @Greg_Deckler 

 

Thanks for the reply. You had recommended a ticketting system, but the system didn't work very well.

 

My problem is a patient can have multiple appointments in a day. Some of our clinical staff do not use the correct codes, and we are needing to isolate these codes. Our software, unless we are looking at every patient, does not locate these codes, and will bill them. As such, we need to isolate, and remove these codes from billing. The reason I have ran the Power Query to Sort, is that I need to have the data in Office, Client, Appt Date, Appt Time order so that I can see if the prior row has a later end time than start time, or if the start times are equal. If they are they are overlapping. It can literally return anything, 1, 2, Yes, No.

 

When comparing the data, and you have 100 patients with 5-10 appointments a day, can earliest be used by isolating the client? Newer to this, and still trying to understand the language and all. Thanks for the continued help!

@andersona1983 - Let's start here, given the sample data provided, what is your expected output?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

My Data listed above has issues, the Appt Date is wrong. Here is the appropriate Data. My Apologies, I was trying to clear private protected info.

 

Client Office NameAppt. StatusStaff NameAppt. DateSubject LineAppointment Start DatetimeAppointment End DatetimeIs RenderedService CodeService CategoryClient Name
CENTRALACTIVEHer, Joh8/3/2020XXXX8/3/2020 11:008/3/2020 14:00Yes

DIR

AAdoe, John
CENTRALACTIVEGar, Tam8/3/2020XXXX8/3/2020 14:008/3/2020 16:00YesDIRAAdoe, John
CENTRALACTIVEMar, Mar8/3/2020XXXX8/3/2020 16:008/3/2020 18:00YesDIRAAdoe, John
CENTRALACTIVEOls, And8/3/2020XXXX8/3/2020 16:308/3/2020 17:00YesDIRAAdoe, John
CENTRALACTIVEHer, Joh8/4/2020XXXX8/4/2020 11:008/4/2020 14:00YesDIRAAdoe, John
CENTRALACTIVEGar, Tam8/4/2020XXXX8/4/2020 14:008/4/2020 16:00YesDIRAAdoe, John
CENTRALACTIVEMar, Mar8/4/2020XXXX8/4/2020 16:008/4/2020 18:00YesDIRAAdoe, John
CENTRALACTIVEHer, Joh8/5/2020XXXX8/5/2020 11:008/5/2020 14:00YesDIRAAdoe, John
CENTRALACTIVEGar, Tam8/5/2020XXXX8/5/2020 14:008/5/2020 16:00YesDIRAAdoe, John
CENTRALACTIVEMar, Mar8/5/2020XXXX8/5/2020 16:008/5/2020 18:00YesDIRAAdoe, John
CENTRALACTIVEOls, And8/5/2020XXXX8/5/2020 16:308/5/2020 17:00YesDIRAAdoe, John
CENTRALACTIVEHer, Joh8/6/2020XXXX8/6/2020 11:008/6/2020 14:00YesDIRAAdoe, John
CENTRALACTIVEGar, Tam8/6/2020XXXX8/6/2020 14:008/6/2020 16:00YesDIRAAdoe, John
CENTRALACTIVEMar, Mar8/6/2020XXXX8/6/2020 16:008/6/2020 18:00YesDIRAAdoe, John
CENTRALACTIVEOls, And8/6/2020XXXX8/6/2020 16:008/6/2020 16:30YesDIRAAdoe, John

 

To list every client with overlapping appointments.

 

In this list it would be:

Client Office NameAppt. StatusStaff NameAppointment Start DatetimeAppointment End DatetimeClient Nam
CENTRALACTIVEOls, And8/3/2020 16:308/3/2020 17:00Adoe, John
CENTRALACTIVEOls, And8/5/2020 16:308/5/2020 17:00Adoe, John
CENTRALACTIVEOls, And8/6/2020 16:008/6/2020 16:30Adoe, John

 

 

@Greg_Deckler 

 

This is for a single patient. My data will included multiple patients some with the same appointment times. Staff members also cross through multple clients.

@andersona1983  - OK, so I think I understand this now. Next question, do you just want to know if an appointment overlaps with any other appointment or are you ultimately looking for a list of appointments that overalap other appointments? Trying to make sure I understand 100% what you are looking to achieve.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

 

Thank you for helping so much! I'm looking for a list of appointments that overalap other appointments.

@andersona1983 - OK, I am thinking something like this:

 

Column = 

  VAR __Table = FILTER('Table',[Appointment Start Datetime] >= EARLIER([Appointment Start Datetime]) && [Appointment End Datetime] <= EARLIER([Appointment End Datetime]))

RETURN

  IF(ISBLANK(__Table),0,1)

 

That isn't the entire solution, I need to think through some things but it is a good place to start. This will return a 1 on rows that have overlapping appointments and 0 for those that don't.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @andersona1983,

 

Feel like I am a little late to the party, but given your data I think the following will provide the 'Prev' columns you need
@andersona1983 - Ammended to include overlap column and removed formatting as the comparison needs to compare the actual dates and not just the time strings. You can format the columns in another column if required

 

Prev End Time = 
    var client = [Client Name]
    var index = [Index]
return
    CALCULATE(MIN('Table'[Appointment End Datetime]), FILTER(ALL('Table'), 'Table'[Client Name] = client && 'Table'[Index] = index -1))

Prev Start Time = 
    var client = [Client Name]
    var index = [Index]
return
    (CALCULATE(MIN('Table'[Appointment Start Datetime]), FILTER(ALL('Table'), 'Table'[Client Name] = client && 'Table'[Index] = index -1)))

Overlap = 
if( 'Table'[Appointment Start Datetime]< 'Table'[Prev End Time] || 'Table'[Appointment Start Datetime] = 'Table'[Prev End Time], "Overlap" , "")

 

 

Hope this Helps,

Richard


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Hi @andersona1983,

 

The expressions provided are DAX, they would be used to create calculated column in your power bi model.

 

Hope this Helps,
Richard


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Thanks.

 

I should clarify, my data is through Excel. My vendor is developing their SQL access. Is this a SQL  expression? Because presently I have all my data in an Excel Database file.

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.