Forum Discussion

Narasimha's avatar
Narasimha
Helper I
9 years ago

DAX Formula for Last 4 weeks data

Hi Team,

Am new to power bi dax, i am trying  to display last 4 weeks data with below dax formula.I have Date table and estalished connection with main table.

 

Last4Weeks = IF(DATEDIFF(ClosedTickets[PResolveDate],TODAY(),WEEK)<5 && WEEKNUM(ClosedTickets[PResolveDate]) <> WEEKNUM(today()),"YES" ,"NO")

 

I got below result with above DAX

 

Getting Extra week with above DAX, please correct me

Need Help

4 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Hi Narasimha

     

    Have you tried 

     

    Last4Weeks = IF(DATEDIFF(ClosedTickets[PResolveDate],TODAY(),WEEK)<4 && WEEKNUM(ClosedTickets[PResolveDate]) <> WEEKNUM(today()),"YES" ,"NO")

    If this works it's probably because the current week is week 0 for the DATEDIFF calc.

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        9 years ago

        Hi Narasimha

         

        I think there is a mis-match on the weekstarting date, so you may want to use the optional argument on the WEEKNUM function based on your week preference

         

        WEEKNUM([date] , 1) = week begins on a Sunday through Saturday (US style)

        WEEKNUM([date] , 2) = week begins on a Monday through Sunday (Rest of the universe style).

         

        So perhaps try variations on

         

        Last4Weeks = IF(IFERROR(DATEDIFF(ClosedTickets[PResolveDate],TODAY(),WEEK),-1)<5 && WEEKNUM(ClosedTickets[PResolveDate],1) <> WEEKNUM(today(),1),"YES" ,"NO")