Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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!
Proud to be a 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...
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?
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 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/4/2020 | XXXX | 8/4/2020 11:00 | 8/4/2020 14:00 | Yes | DIR | A | Adoe, John |
CENTRAL | ACTIVE | Gar, Tam | 8/4/2020 | XXXX | 8/4/2020 14:00 | 8/4/2020 16:00 | Yes | DIR | A | Adoe, John |
CENTRAL | ACTIVE | Mar, Mar | 8/4/2020 | XXXX | 8/4/2020 16:00 | 8/4/2020 18:00 | Yes | DIR | A | Adoe, John |
CENTRAL | ACTIVE | Her, Joh | 8/5/2020 | XXXX | 8/5/2020 11:00 | 8/5/2020 14:00 | Yes | DIR | A | Adoe, John |
CENTRAL | ACTIVE | Gar, Tam | 8/5/2020 | XXXX | 8/5/2020 14:00 | 8/5/2020 16:00 | Yes | DIR | A | Adoe, John |
CENTRAL | ACTIVE | Mar, Mar | 8/5/2020 | XXXX | 8/5/2020 16:00 | 8/5/2020 18:00 | Yes | DIR | A | Adoe, John |
CENTRAL | ACTIVE | Ols, And | 8/5/2020 | XXXX | 8/5/2020 16:30 | 8/5/2020 17:00 | Yes | DIR | A | Adoe, John |
CENTRAL | ACTIVE | Her, Joh | 8/6/2020 | XXXX | 8/6/2020 11:00 | 8/6/2020 14:00 | Yes | DIR | A | Adoe, John |
CENTRAL | ACTIVE | Gar, Tam | 8/6/2020 | XXXX | 8/6/2020 14:00 | 8/6/2020 16:00 | Yes | DIR | A | Adoe, John |
CENTRAL | ACTIVE | Mar, Mar | 8/6/2020 | XXXX | 8/6/2020 16:00 | 8/6/2020 18:00 | Yes | DIR | A | Adoe, John |
CENTRAL | ACTIVE | Ols, And | 8/6/2020 | XXXX | 8/6/2020 16:00 | 8/6/2020 16:30 | Yes | DIR | A | Adoe, John |
To list every client with overlapping appointments.
In this list it would be:
Client Office Name | Appt. Status | Staff Name | Appointment Start Datetime | Appointment End Datetime | Client Nam |
CENTRAL | ACTIVE | Ols, And | 8/3/2020 16:30 | 8/3/2020 17:00 | Adoe, John |
CENTRAL | ACTIVE | Ols, And | 8/5/2020 16:30 | 8/5/2020 17:00 | Adoe, John |
CENTRAL | ACTIVE | Ols, And | 8/6/2020 16:00 | 8/6/2020 16:30 | Adoe, John |
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.
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.
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!
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!
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.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |