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

Dax formula for Last Thursday

Hey Guys, 

 

I have a date table and I need to get a formula to get a custom date, such as last Thursday or Monday. Tried WEEKNUM function with max operator as well, did not seem wokring. 

 

Can you please help. 

 

Thanks, 

Sona

1 ACCEPTED SOLUTION
az38
Community Champion
Community Champion

Hi @Anonymous 

you need smth like

 

Measure = 
CALCULATE(MAX(Table[Date]), Table[Date]<TODAY(), WEEKDAY(Table[Date])=4)

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

4 REPLIES 4
camargos88
Community Champion
Community Champion

Hi @Anonymous ,

 

Try this code:

 

Monday = CALCULATE(MAX('Date'[Date]); FILTER(ALL('Date'); 'Date'[Date] < MAX('Date'[Date]) && WEEKDAY('Date'[Date]; 1) = 2))
 
If you need change the day, just change WEEKDAY('Date'[Date]; 1) = 2 (Starting from Sunday = 1).
 
Ricardo
 
 


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Greg_Deckler
Super User
Super User

Well, if I had a date table and I needed to get last Thursday in a column for example for each date, I would probaby do something along the lines of:

 

Last Thursday Column =
  VAR __Date = 'Calendar'[Date]
  VAR __Weeknum = WEEKNUM(__Date)
  VAR __PreviousWeekNum = __Weeknum - 1
RETURN
  MAXX(FILTER('Calendar',WEEKNUM([Date]) = __PreviousWeekNum && WEEKDAY([Date]) = 5),[Date])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
az38
Community Champion
Community Champion

Hi @Anonymous 

you need smth like

 

Measure = 
CALCULATE(MAX(Table[Date]), Table[Date]<TODAY(), WEEKDAY(Table[Date])=4)

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

It worked great. Thanks a lot. 

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.