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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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