Forum Discussion
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
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
- MariuszCommunity Champion
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_knightPost Prodigy
Brilliant. Thank you Mariusz
- MariuszCommunity Champion