Forum Discussion
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 Name | Appt. Status | Staff Name | Appt. Date | Subject Line | Appointment Start Datetime | Appointment End Datetime | Is Rendered | Service Code | Service Category | Client Name |
| CENTRAL | ACTIVE | Her, Joh | 8/3/2020 | XXXX | 8/3/2020 11:00 | 8/3/2020 14:00 | Yes | DIR | A | Adoe, John |
| CENTRAL | ACTIVE | Gar, Tam | 8/3/2020 | XXXX | 8/3/2020 14:00 | 8/3/2020 16:00 | Yes | DIR | A | Adoe, John |
| CENTRAL | ACTIVE | Mar, Mar | 8/3/2020 | XXXX | 8/3/2020 16:00 | 8/3/2020 18:00 | Yes | DIR | A | Adoe, John |
| CENTRAL | ACTIVE | Ols, And | 8/3/2020 | XXXX | 8/3/2020 16:30 | 8/3/2020 17:00 | Yes | DIR | A | Adoe, John |
| CENTRAL | ACTIVE | Her, Joh | 8/3/2020 | XXXX | 8/4/2020 11:00 | 8/4/2020 14:00 | Yes | DIR | A | Adoe, John |
| CENTRAL | ACTIVE | Gar, Tam | 8/3/2020 | XXXX | 8/4/2020 14:00 | 8/4/2020 16:00 | Yes | DIR | A | Adoe, John |
| CENTRAL | ACTIVE | Mar, Mar | 8/3/2020 | XXXX | 8/4/2020 16:00 | 8/4/2020 18:00 | Yes | DIR | A | Adoe, John |
| CENTRAL | ACTIVE | Her, Joh | 8/3/2020 | XXXX | 8/5/2020 11:00 | 8/5/2020 14:00 | Yes | DIR | A | Adoe, John |
| CENTRAL | ACTIVE | Gar, Tam | 8/3/2020 | XXXX | 8/5/2020 14:00 | 8/5/2020 16:00 | Yes | DIR | A | Adoe, John |
| CENTRAL | ACTIVE | Mar, Mar | 8/3/2020 | XXXX | 8/5/2020 16:00 | 8/5/2020 18:00 | Yes | DIR | A | Adoe, John |
| CENTRAL | ACTIVE | Ols, And | 8/3/2020 | XXXX | 8/5/2020 16:30 | 8/5/2020 17:00 | Yes | DIR | A | Adoe, John |
| CENTRAL | ACTIVE | Her, Joh | 8/3/2020 | XXXX | 8/6/2020 11:00 | 8/6/2020 14:00 | Yes | DIR | A | Adoe, John |
| CENTRAL | ACTIVE | Gar, Tam | 8/3/2020 | XXXX | 8/6/2020 14:00 | 8/6/2020 16:00 | Yes | DIR | A | Adoe, John |
| CENTRAL | ACTIVE | Mar, Mar | 8/3/2020 | XXXX | 8/6/2020 16:00 | 8/6/2020 18:00 | Yes | DIR | A | Adoe, John |
| CENTRAL | ACTIVE | Ols, And | 8/3/2020 | XXXX | 8/6/2020 16:00 | 8/6/2020 16:30 | Yes | DIR | A | Adoe, John |
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 requiredPrev 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!
11 Replies
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/339586
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?
