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.
  • 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

      • ategally's avatar
        ategally
        Frequent Visitor
        8 years ago

        Thank you Anonymous

         

        I have implemented this and all I get is the same data for each day:

         

        02/10/2017 00:00:008421206.68822962675.768
        13/10/2017 00:00:008421206.68822962675.768
        15/10/2017 00:00:008421206.68822962675.768
        10/10/2017 00:00:008421206.68822962675.768
        12/10/2017 00:00:008421206.68822962675.768
        14/10/2017 00:00:008421206.68822962675.768
        09/10/2017 00:00:008421206.68822962675.768
        11/10/2017 00:00:008421206.68822962675.768
        01/01/2017 00:00:008421206.68822962675.768