topic How to compare today's date and specific time with another date column in DAX Commands and Tips

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

Anonymous — Wed, 20 Nov 2024 15:29:53 GMT

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

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

bhanu_gautam — Wed, 20 Nov 2024 15:53:44 GMT

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.

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

Kedar_Pande — Wed, 20 Nov 2024 17:15:56 GMT

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

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

Anonymous — Thu, 21 Nov 2024 02:55:31 GMT

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.

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

Anonymous — Thu, 21 Nov 2024 14:19:53 GMT

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

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

Anonymous — Thu, 21 Nov 2024 14:20:31 GMT

Thanks for your response. I will try using this code

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

Anonymous — Thu, 21 Nov 2024 14:21:56 GMT

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