How to compare today's date and specific time with another date column

[Anonymous]

Hi Team,

Can you please advise how to address this issue.

I need to get 3 flags as below. Start Date is my columj in data source which has date and time in it.

• Set flag to Yes if Start date is from current date 9 pm to current date +7days  6 am
•  Set flag Yes if start date is from Tuesday next week 9 pm till Tuesday of Next week + 28 days at 6 am
• Set flag Yes if start date  is from Friday of current week 9 pm till Friday of current + 25 days at 6 am

Really appreciate if someone can help

[Anonymous]

Hi @Anonymous 

Thanks for the reply from bhanu_gautam and Kedar_Pande .

ashish_18, you can also try the following:

My sample:

Create three calculated columns as follows.

Flag 1 = 
VAR _CurrentDate = TODAY()
VAR _StartTime = TIME(21, 0, 0)
VAR _EndTime = TIME(6, 0, 0)
VAR _StartDateTime = _CurrentDate + _StartTime
VAR _EndDateTime = _CurrentDate + 7 + _EndTime
RETURN 
IF(
    'Table'[Start Date] >= _StartDateTime && 'Table'[Start Date] <= _EndDateTime,
    "Yes",
    "No"
)

Flag 2 = 
VAR _CurrentDate = TODAY()
VAR _StartTime = TIME(21, 0, 0)
VAR _EndTime = TIME(6, 0, 0)
VAR _DaysToNextTuesday = MOD(2 - WEEKDAY(_CurrentDate, 2) + 7, 7)
VAR _TuesdayNextWeek = _CurrentDate + _DaysToNextTuesday
VAR _StartDateTime = _TuesdayNextWeek + _StartTime
VAR _EndDateTime = _TuesdayNextWeek + 28 + _EndTime
RETURN 
IF(
    'Table'[Start Date] >= _StartDateTime && 'Table'[Start Date] <= _EndDateTime,
    "Yes",
    "No"
)

Flag 3 = 
VAR _CurrentDate = TODAY()
VAR _StartTime = TIME(21, 0, 0)
VAR _EndTime = TIME(6, 0, 0)
VAR _DaysToFriday = MOD(5 - WEEKDAY(_CurrentDate, 2) + 7, 7)
VAR _FridayThisWeek = _CurrentDate + _DaysToFriday
VAR _StartDateTime = _FridayThisWeek + _StartTime
VAR _EndDateTime = _FridayThisWeek + 25 + _EndTime
RETURN 
IF(
    'Table'[Start Date] >= _StartDateTime && 'Table'[Start Date] <= _EndDateTime,
    "Yes",
    "No"
)

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for your response. I will try using this code

[Kedar_Pande · Accepted solution]

@Anonymous 

Create three calculated columns:

Flag_1 = 
VAR CurrentDate = TODAY()
VAR StartWindow = CurrentDate + TIME(21, 0, 0) // Current date 9 PM
VAR EndWindow = CurrentDate + 7 + TIME(6, 0, 0) // Current date +7 days 6 AM
RETURN
IF (
'Table'[Start Date] >= StartWindow &&
'Table'[Start Date] <= EndWindow,
"Yes",
"No"
)

Flag_2 = 
VAR CurrentDate = TODAY()
VAR DaysToTuesday = 3 - WEEKDAY(CurrentDate, 2) // Get days to next Tuesday (2-based week)
VAR NextTuesday = CurrentDate + IF(DaysToTuesday < 0, DaysToTuesday + 7, DaysToTuesday)
VAR StartWindow = NextTuesday + TIME(21, 0, 0) // Next Tuesday 9 PM
VAR EndWindow = NextTuesday + 28 + TIME(6, 0, 0) // +28 days 6 AM
RETURN
IF (
'Table'[Start Date] >= StartWindow &&
'Table'[Start Date] <= EndWindow,
"Yes",
"No"
)

Flag_3 = 
VAR CurrentDate = TODAY()
VAR DaysToFriday = 5 - WEEKDAY(CurrentDate, 2) // Get days to Friday (2-based week)
VAR ThisFriday = CurrentDate + IF(DaysToFriday < 0, DaysToFriday + 7, DaysToFriday)
VAR StartWindow = ThisFriday + TIME(21, 0, 0) // This Friday 9 PM
VAR EndWindow = ThisFriday + 25 + TIME(6, 0, 0) // +25 days 6 AM
RETURN
IF (
'Table'[Start Date] >= StartWindow &&
'Table'[Start Date] <= EndWindow,
"Yes",
"No"
)

Let me know if you'd like further refinements!

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Anonymous]

HI @Kedar_Pande  Thansk for your response. I am creating claulated columns as u stated. Fisrt flag works fine but other 2 I could not test due to no data. I will keep validating it and if I face any issue ,I will let u know..thnx again

[bhanu_gautam]

@Anonymous , Try using below steps

Create a calculated column for the current date and time:
CurrentDateTime = NOW()

Create a calculated column for the start of the day (9 PM) and end of the day (6 AM) for the current date:
CurrentDate9PM = DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY())) + TIME(21, 0, 0)
CurrentDatePlus7Days6AM = DATE(YEAR(TODAY() + 7), MONTH(TODAY() + 7), DAY(TODAY() + 7)) + TIME(6, 0, 0)

Create a calculated column for the start and end of the period for Tuesday next week:
NextTuesday9PM = DATE(YEAR(TODAY() + (8 - WEEKDAY(TODAY(), 2))), MONTH(TODAY() + (8 - WEEKDAY(TODAY(), 2))), DAY(TODAY() + (8 - WEEKDAY(TODAY(), 2)))) + TIME(21, 0, 0)
NextTuesdayPlus28Days6AM = DATE(YEAR(TODAY() + (8 - WEEKDAY(TODAY(), 2)) + 28), MONTH(TODAY() + (8 - WEEKDAY(TODAY(), 2)) + 28), DAY(TODAY() + (8 - WEEKDAY(TODAY(), 2)) + 28)) + TIME(6, 0, 0)

Create a calculated column for the start and end of the period for Friday of the current week:
ThisFriday9PM = DATE(YEAR(TODAY() + (6 - WEEKDAY(TODAY(), 2))), MONTH(TODAY() + (6 - WEEKDAY(TODAY(), 2))), DAY(TODAY() + (6 - WEEKDAY(TODAY(), 2)))) + TIME(21, 0, 0)
ThisFridayPlus25Days6AM = DATE(YEAR(TODAY() + (6 - WEEKDAY(TODAY(), 2)) + 25), MONTH(TODAY() + (6 - WEEKDAY(TODAY(), 2)) + 25), DAY(TODAY() + (6 - WEEKDAY(TODAY(), 2)) + 25)) + TIME(6, 0, 0)

Create the flags based on the conditions:
DAX
Flag1 = IF(
'Table'[Start Date] >= [CurrentDate9PM] && 'Table'[Start Date] <= [CurrentDatePlus7Days6AM],
"Yes",
"No"
)

Flag2 = IF(
'Table'[Start Date] >= [NextTuesday9PM] && 'Table'[Start Date] <= [NextTuesdayPlus28Days6AM],
"Yes",

These calculated columns will set the flags based on the specified date and time ranges. Make sure to replace 'Table' with the actual name of your table in Power BI.

[Anonymous]

Thanks @bhanu_gautam , I will try on this code as well. As of now,I tried other 2 solution and its working for me. As my validations not done yet,I will refer these as well