Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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.
Really appreciate if someone can help
Solved! Go to Solution.
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 @ashish_18
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.
Thanks for your response. I will try using this code
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
@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.
Proud to be a Super User! |
|
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
25 | |
24 | |
12 | |
12 | |
11 |