cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Create column with DAX to determine if date is the current week or not.

I have a date column I would like to evaluate and create another column that determines if the date is in today's week or not. How can I do this?

 

In the below example the column "Load Date (including STO)" I want to evaluate. Today is Tuesday 4/21/20 so the current week is Monday 4/20/20 - Sunday 4/27/20. Any days that are in the current week I want my new column to display, "Current Week" otherwise "Not Current Week"

 

this week.png

1 ACCEPTED SOLUTION
edhans
Super User
Super User

Use this

Is This Week = WEEKNUM([Date],1) = WEEKNUM(TODAY(),1)

It will return true or false. The ,1 at the end determines how to define a week:

 

Return type: 1, week begins on Sunday. Weekdays are numbered 1 through 7.

Return type: 2, week begins on Monday. Weekdays are numbered 1 through 7. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
Netjacker65
Frequent Visitor

How i would do it is to create two new column.
 
Frist:
 
Weekdaterange = WEEKNUM('Yourdatasheetname'[Load Date (including STO)])
 
second:
 

Current Week =
VAR IsYear = IF( YEAR( TODAY() ) = VALUE( 'Date'[Year] ), TRUE(), FALSE() )
VAR IsWeek = IF( WEEKNUM( TODAY() ) = 'Date'[Weekdaterange], TRUE(), FALSE() )

RETURN
IF( AND( IsYear, IsWeek ), TRUE(), FALSE() )
RETURN
IF( AND( IsYear, IsWeek ), TRUE(), FALSE() )

 

change true to ""Current Week" and false "Not Current Week"

 

should work i guess.

 

Maybe a bit late, but this solution won't work very well.

Weeknr are nog fully matched with years. So week 1 of 2020 can start on 30th dec of 2019. 

So it is 2020 Week 1 but date is 30-12-2019. When viewing your report on 2nd of january, it will not show me the first 2 days of the current week, this can really give wrong information.... when working with dates and weeks, you should always use a separate yearcolumn to attacht to the weeks, to do this properly.  I always have a year-month combination and a separate year-week combination.

 

Good luck!!

edhans
Super User
Super User

Use this

Is This Week = WEEKNUM([Date],1) = WEEKNUM(TODAY(),1)

It will return true or false. The ,1 at the end determines how to define a week:

 

Return type: 1, week begins on Sunday. Weekdays are numbered 1 through 7.

Return type: 2, week begins on Monday. Weekdays are numbered 1 through 7. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.