Current Time - Return Score based on Current Time

[michael_knight]

Hi,

I'm currently working with this piece of DAX code

Current Day - Current Value = 
var MondThursSat = {"Monday", "Tuesday", "Wednesday", "Thursday", "Saturday"}
var FriSun ={"Friday", "Sunday"}

return
IF(
    'Active Leads'[Current day of the week]IN MondThursSat,
    SWITCH( TRUE(),
        'Active Leads'[Now - Time] >= TIME(18,30,00), "7",
        'Active Leads'[Now - Time] >= TIME(18,00,00), "6",
        'Active Leads'[Now - Time] >= TIME(16,30,00), "5",
        'Active Leads'[Now - Time] >= TIME(15,00,00), "5",
        'Active Leads'[Now - Time] >= TIME(13,30,00), "4",
        'Active Leads'[Now - Time] >= TIME(12,00,00), "3",
        'Active Leads'[Now - Time] >= TIME(10,30,00), "2",
        'Active Leads'[Now - Time] >= TIME(08,00,00), "1",
        'Active Leads'[Now - Time] >= TIME(00,00,01), "0"),
IF(
 'Active Leads'[Weekday] IN FriSun,
    SWITCH( TRUE(),
        'Active Leads'[Now - Time] >= TIME(17,00,00), "6",
        'Active Leads'[Now - Time] >= TIME(16,30,00), "6",
        'Active Leads'[Now - Time] >= TIME(15,00,00), "5",
        'Active Leads'[Now - Time] >= TIME(13,30,00), "4",
        'Active Leads'[Now - Time] >= TIME(12,00,00), "3",
        'Active Leads'[Now - Time] >= TIME(10,30,00), "2",
        'Active Leads'[Now - Time] >= TIME(08,00,00), "1",
        'Active Leads'[Now - Time] >= TIME(00,00,01), "0")
))

What this code is doing is detemining what day of the week we're currently on (Tuesday today), seeing what the current time is and then giving points depending on the time of day

As I'm writing this post it's current 15:24 on a Tuesday, which means the score returned should be 5, however I'm getting 7 as my returning value

Here's a picture of the data, I'll link a dropbox below the picture incase you want a better look

https://www.dropbox.com/s/sh7za59gg8ubj84/Help%20-%20Calls.pbix?dl=0

Does anyone know where the problem my lie?

Any help appreciated

Cheers,

Mike

[Mariusz · Accepted solution]

Hi @michael_knight 

Try using the below, 

TIMEVALUE( 'Active Leads'[Now - Time] ) >= TIME( 18, 30, 00 )

 if you changed only the Data Type to Time this is not enough as the actual underlying value is date-time and you need to isolate time from it

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[michael_knight]

Brilliant. Thank you @Mariusz 

[Mariusz]

Hi @michael_knight 

No problem!