WTD Last Year Calculation

[Anonymous]

Hello, 

I am trying to compute WTD Last Year for Sales Data. I have computer WTD this year using either or these formulas: 

WTD TY Sales = CALCULATE(SUM([Dollars_Sold]),DATESINPERIOD(Sales[Calendar Date],LastDate(Sales[Calendar Date]),-WEEKDAY(LastDate(Sales[Calendar Date])),DAY))

or

WTD TY Sales = CALCULATE(SUM(Sales[Dollars_Sold]),LASTDATE(Sales[Calendar Date]))

I have tried to adjust these formulas to show me same WeekNumber last year (ie 2017), but I have not been able to do it. Does anyone know how I can compute it?

[v-jiascu-msft]

Hi @Anonymous,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[kazael]

Are you trying to present sales between the same week number last year and current date?

[Anonymous]

Yes! We are trying to present sales during the same week TY vs. LY

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Please check out the demo in the attachment and try the measure like this.

Measure =
CALCULATE (
    SUM ( FactSales[salesquantity] ),
    FILTER (
        ALL ( 'Calendar' ),
        YEAR ( [Date] )
            = YEAR ( MIN ( [Date] ) ) - 1
            && 'Calendar'[WeekNum] = MIN ( 'Calendar'[WeekNum] )
    )
)

Best Regards,
Dale

[Chihiro]

It is near impossible to align weeks perfectly over different year.

Ex:

Using WEEKNUM([Date], 21) i.e. ISO 8601 week number.

Year 2020 will have week number 53. Where as 2017 to 2019 will have 52.

When comparing different years, I tend to use one of following methods depending on the need.

1) Average of value by weekday in a given month of year and compare to previous year.

2) Compare at monthly level

3) Compare based on day of year (i.e. 1~365/366), if there is no intra week pattern.

4) Compare week over week, without year constraint.

etc.

[Anonymous]

We are in a retail environment, so we are using a 4-5-4 calendar. 

So we will be able to compare the same week number in different years. 

[Chihiro]

Hmm? Would some year not have 53 weeks instead of 52 weeks in 4-5-4 format still? Occuring every 5 to 6 years?

Do you have dimension table where 2017 is restated (i.e. ignoring first week of the year 2017)? Or is 53rd week being ignored?

At any rate, it would help if you have model along with calendar dimension table (4-5-4 mapping).

[Anonymous]

It is restated, so the dates may not match exactly, but the week number will remain the same. 

[Chihiro]

So how is your table structured?

Without it, bit hard to give you exact formula. Can you upload sample?

fyi - if you don't have calendar dimension table. I'd recommend adding one. It makes time intelligence calculation so much easier to do.

As an example. If you have WeekNum, Year, WeekDay columns in fact table (without calendar)...

Then you'd do something like.

CurrentWk = CALCULATE(Max('Table'[WeekNum]),Filter(ALL('Table'),'Table'[Date]=MAX('Table'[Date])))

WTD Current = CALCULATE(SUM('Table'[Value]),Filter('Table',[WeekNum]=[CurrentWk]))

WTD LastYr = CALCULATE(SUM('Table'[Value]),Filter('Table',[WeekNum]=[CurrentWk]&&[Year]=Year(MAX('Table'[Date]))-1&&[WeekDay]<=WEEKDAY(MAX('Table'[Date]),2)))

[Anonymous]

Calendar_Date

Calendar_Date_String

Calendar_Month

Calendar_Day

Calendar_Year

Calendar_Quarter

Day_Name

Day_of_Week

Day_of_Week_in_Month

Day_of_Week_in_Year

Day_of_Week_in_Quarter

Day_of_Quarter

Day_of_Year

Week_of_Month

Week_of_Quarter

Week_of_Year

Month_Name

First_Date_of_Week

Last_Date_of_Week

First_Date_of_Month

Last_Date_of_Month

First_Date_of_Quarter

Last_Date_of_Quarter

First_Date_of_Year

Last_Date_of_Year

Is_Leap_Year

Days_in_Month

Merchant_Day_of_Year

Merchant_Week_of_Year

Merchant_Calendar_Month

Merchant_Calendar_Quarter

Merchant_First_Date_of_Week

Merchant_Last_Date_of_Week

Merchant_First_Date_of_Month

Merchant_Last_Date_of_Month

Merchant_First_Date_of_Quarter

Merchant_Last_Date_of_Quarter

Merchant_First_Date_of_Year

Merchant_Last_Date_of_Year

ISO_Day_of_Week

ISO_Day_of_Year

ISO_Week_of_Year

ISO_First_Date_of_Week

ISO_Last_Date_of_Week

ISO_String

Fiscal_Day_of_Year

Fiscal_Week_of_Year

Fiscal_Quarter

Fiscal_Month

Fiscal_First_Date_of_Year

Fiscal_Last_Date_of_Year

Season

Sunday, August 6, 2017

8/6/2017

8

6

2017

3

Sunday

1

1

32

6

37

218

2

6

32

August

Sunday, August 6, 2017

Saturday, August 12, 2017

Tuesday, August 1, 2017

Thursday, August 31, 2017

Saturday, July 1, 2017

Saturday, September 30, 2017

Sunday, January 1, 2017

Sunday, December 31, 2017

False

31

190

28

7

3

Sunday, August 6, 2017

Saturday, August 12, 2017

Sunday, July 30, 2017

Saturday, August 26, 2017

Sunday, July 30, 2017

Saturday, October 28, 2017

Sunday, January 29, 2017

Saturday, February 3, 2018

7

217

31

Monday, July 31, 2017

Sunday, August 6, 2017

2017-W31-7

310

46

4

11

Saturday, October 1, 2016

Saturday, September 30, 2017

Fall

Above is an example of the schema.

I tried the formulas, the WTD TY worked but the WTD LY gives me no result. Here are the formulas I used.

Current Week = CALCULATE(MAX('Date'[Merchant_Week_of_Year]), FILTER(ALL('Sales'), Sales[Calendar Date] = MAX(Sales[Calendar Date])))

Last Year = YEAR(MAX(Sales[Calendar Date]))-1

WTD TY Test = CALCULATE(SUM(Sales[Dollars_Sold]),FILTER('Date',[Merchant_Week_of_Year]=[Current Week]))

WTD LY Test = CALCULATE(SUM(Sales[Dollars_Sold]),FILTER('Date',[Merchant_Week_of_Year]=[Current Week]&&'Date'[Calendar Year]=[Last Year]&&'Date'[Day_of_Week]<=WEEKDAY(MAX(Sales[Calendar Date]),2)))

[Chihiro]

Can you upload dummy data to cloud share storage? With desensitized info and just enough data to demonstrate your need.

It will be so much easier to help you with it.