Calculated Column showing True if specific date column shows date within the next 3 weeks

[D_Brandon_E]

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.

[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

[Anonymous · Accepted solution]

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

[amitchandak]

@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()
)