Forum Discussion

ategally's avatar
ategally
Frequent Visitor
8 years ago

This Week and Last Week

I need to create a base derivative for This Week and Last Week so I can add this to any base measure to give for example Sales for This Week or Sales for Last Week.

 

Please help.

5 Replies

    • ategally's avatar
      ategally
      Frequent Visitor
      8 years ago

      Hi Anonymous

       

      Thank you for your response.  Greatly appreciated.

       

      However I came up with this and not sure how to apply this to say the Sales measure:

       

      [IsInCurrentYear] =if(YEAR(NOW())= [Dates[Calendar Year],1,0)



      --This Week
       
       [This Week] =if([isInCurrentYear] && WEEKNUM(NOW())=[Dates[Week Year No],1,0)

      --Last Week
       
       [Last Week] =if([isInCurrentYear] && (WEEKNUM(NOW())-1)=[Dates[Week Year No],1,0)

       

       

      Kindest regards

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        HI ategally,

         

         

        Maybe you can take a look at below formula:

        This Week Sales =
CALCULATE (
    SUM ( table[Sales] ),
    FILTER (
        ALL ( Date ),
        YEAR ( Date[Date] ) = YEAR ( TODAY () )
            && WEEKNUM ( Date[Date], 1 ) = WEEKNUM ( TODAY (), 1 )
    )
)

Last Week Sales =
CALCULATE (
    SUM ( table[Sales] ),
    FILTER (
        ALL ( Date ),
        YEAR ( Date[Date] ) = YEAR ( TODAY () )
            && WEEKNUM ( Date[Date], 1 ) = WEEKNUM ( TODAY (), 1 )-1
    )
)

        Regards,

        Xiaoxin Sheng