Solved: Checking Time Values - Page 2

andersona1983 · ‎08-10-2020

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

richbenmintz · ‎08-11-2020

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