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