Forum Discussion

michael_knight's avatar
michael_knight
Post Prodigy
6 years ago
Solved

Current Time - Return Score based on Current Time

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

  • 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

     

3 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion

    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