Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

Tips and Tricks
  • az38's avatar
    az38
    6 years ago

    Hi Anonymous 

    you need smth like

     

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

     

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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])
  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

    you need smth like

     

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      It worked great. Thanks a lot. 

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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