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
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi ategally,

       

      My formulas are measure, I think you use them as the calcualte column,right?

      In addition, if you share some sample data, it will be help for analysis.

       

      Regards,

      Xiaoxin Sheng