Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
ashish_18
Helper III
Helper III

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

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

1 ACCEPTED SOLUTION
Kedar_Pande
Super User
Super User

@ashish_18 

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

View solution in original post

6 REPLIES 6
v-xuxinyi-msft
Community Support
Community Support

Hi @ashish_18 

 

Thanks for the reply from bhanu_gautam and Kedar_Pande .

 

ashish_18, you can also try the following:

 

My sample:

vxuxinyimsft_0-1732157266859.png

 

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:

vxuxinyimsft_1-1732157520090.png

 

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.

Thanks for your response. I will try using this code

Kedar_Pande
Super User
Super User

@ashish_18 

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

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
Super User
Super User

@ashish_18 , 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.




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.