This Week and Last Week

[ategally]

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]

Hi @ategally,

I think weeknum function will suitable for your requirement.

Reference link:

WEEKNUM Function (DAX)

Custom Week filter

Regards,

Xiaoxin Sheng

[ategally]

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]

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]

Thank you @Anonymous

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

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

[Anonymous]

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