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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors