Forum Discussion
Calculated Column showing True if specific date column shows date within the next 3 weeks
Hi everyone,
I am working with a dataset that has multiple date columns in one table. They are all linked to a Calendar table and If I need to calculate something based on a specific date column, I have had to use USERELATIONSHIP. With that said, I wanted to create a calculated column that shows TRUE if the date entered in a specific column is within the next 3 weeks. The kicker is the start date needs to be the first date of the current week and then the end date needs to be the last day of 3 weeks out. I have a calendar table with week offset, start of week, and end of week, but any try at doing an IF statement with the column being >= startdate and <= enddate is not working properly. It won't throw an error, but all rows blank for that column. I should have 2 rows that show 2 from my validation.
I tried creating variables for each of the dates I needed using the calculate function but ran into the issue with text to date conversion. I then tried DatesBetween, but same issue.
Any ideas. I think I have been looking at PBI took long this week and my brain is mush and I can't think through this one. Any nudge in the right direction would be great.
An example of what I need is:
IF(
Table[Date Column] >= (Start Date from current week) && <= (End Date from 3 weeks from now), TRUE(), FALSE()
)
The start date is in the Date table and so is the End of Week.
Hi D_Brandon_E ,
Please try to create a new calculated column with the following expression.
Column = VAR _Start_Date_from_current_week = TODAY () - WEEKDAY ( TODAY () ) + 1 VAR _End_Date_from_3_week_from_now = TODAY () - WEEKDAY ( TODAY () ) + 21 RETURN IF ( 'Table'[Date Column] >= _Start_Date_from_current_week && 'Table'[Date Column] <= _End_Date_from_3_week_from_now, TRUE (), FALSE () )You can edit the expression to get different week start and week end dates.
Hope it helps.Best Regards,
Community Support Team_Gao
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
4 Replies
- amitchandak
Super User
D_Brandon_E , a new column
new column
var _wk_strat = today() -1* WEEKDAY(today(),2) +1
var _wk_end= today() -1* WEEKDAY(today(),2) +7
return
IF(
Table[Date Column] >=_wk_strat && <= _wk_end +14 , TRUE(), FALSE()
)or
new column
var _wk_strat = today() -1* WEEKDAY(today(),1) +1 //sunday week
var _wk_end= today() -1* WEEKDAY(today(),1) +7
return
IF(
Table[Date Column] >=_wk_strat && <= _wk_end +14 , TRUE(), FALSE()
) - TheoC
Community Champion
Hi D_Brandon_E
Can you try something like the following:
Column =
VAR _1 = 'Table'[Date] - WEEKDAY ( 'Table'[Date] , 3 )
VAR _2 = _1 + 21-1
RETURN
IF ( 'Table'[Date] >= _1 && 'Table'[Date] <= _2 , TRUE() , FALSE() )Just make sure to change the table / column names to match yours.
Hope this helps.
Theo
Hi D_Brandon_E ,
Please try to create a new calculated column with the following expression.
Column = VAR _Start_Date_from_current_week = TODAY () - WEEKDAY ( TODAY () ) + 1 VAR _End_Date_from_3_week_from_now = TODAY () - WEEKDAY ( TODAY () ) + 21 RETURN IF ( 'Table'[Date Column] >= _Start_Date_from_current_week && 'Table'[Date Column] <= _End_Date_from_3_week_from_now, TRUE (), FALSE () )You can edit the expression to get different week start and week end dates.
Hope it helps.Best Regards,
Community Support Team_Gao
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!- TheoC
Hi @D_Brandon_E
Can you try something like the following:
Column =
VAR _1 = 'Table'[Date] - WEEKDAY ( 'Table'[Date] , 3 )
VAR _2 = _1 + 21-1
RETURN
IF ( 'Table'[Date] >= _1 && 'Table'[Date] <= _2 , TRUE() , FALSE() )Just make sure to change the table / column names to match yours.
Hope this helps.
Theo
