cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: Weekday Last Year Comparison

Hi @VijayReddy

 

As we want to compare by day by week and year, we had to use a formula to generate the unique number combination.

So,

Create a column DWY = ([WeekDayNo]*100+[WeekNo])*10000+[Year] . This will create numbers like 5012015, 5012016, etc. etc

    depending on the Date.

 

When we use the measure to compute the sales for same day same week last year as

     SalesLYDWY = Calculate([Sales], Filter(ALL(Calendar),Calendar[DWY] = Max( Calendar[DWY]) -1 ))

 

it becomes simpler to just substract 1 from the current selected calendar year.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Anonymous
Not applicable

Re: Weekday Last Year Comparison

Hello,

 

I have used your solution, it works great. The only issue is that now I am trying to aggregate it per month as well.

 

To get the sales for last year I am using this:

 

Sales Last Year = SUMX(VALUES('Calendar'[DWY]),Calculate(SUM('Sales & Tickets'[Sales]), Filter(ALL('Calendar'),Calendar[DWY] = MAX(Calendar[DWY])-1 )))

 

Unfortunately when drilling through from day to month data is slightly inaccurate. Any idea how to solve this issue?

 

Again, thank you for your previous solution, it was extremely helpful!

Anonymous
Not applicable

Re: Weekday Last Year Comparison

For those interested, I replaced the MAX by the function EARLIER that I just discovered (which by my understanding we could actually almost always call current row).