Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jpereztang
Helper I
Helper I

Weekday Last Year Comparison

Hi everybody !

 

I have a requeriment that i don't know how to achieve. Question is simple, how can I compare this year sales with exactly the same weekday sales of last year. take for instance, Today is July 1st 2016 i must compare today sales with sales on 29th June 2015 because this two days are monday of the same week.

 

You can think that's piece of cake, just DATEADD -365 days or DATEADD -1 year but I have to say there are some years that doesn't have 365days. For instance, this febrary have 29 days and febrary of last year have just 28, aka leap-year.

I think this could resolve my problem but actually dax doesn't accept 'week' as parameter

 

Ventas cliente preferente YTD:=CALCULATE(
                                                                          [Ventas cliente preferente]
                                                                         , DATEADD(Tiempo[Fecha],-52,week)
)

I hope you can help me 

Thanks in advance

 

 

 

20 REPLIES 20
vapid128
Solution Specialist
Solution Specialist

YOUR DATE LIKE THIS TABLE:

create weeknum and year colnum

vapid128_0-1645060540276.png

and

SUMMARIZE(weeknum,year,"total sales", sum(sales))
 
 
vapid128_1-1645060648014.png

last year = lookupvalue([total sales],[weeknum],[weeknum],[year],[year]-1)

vapid128
Solution Specialist
Solution Specialist

AND question about your weeknum.

 

It is depends on how you want to read your week. 

You week is from Monday to Sunday.

if you feel the week number may be some problem.

You can try make 01/01 - 01/07 of each year to week number 1, and  08 - 14 to week number 2.

weeknum = int(([date] - DATE(year([date],1,1)+1)/7)

 

stiggrr87
Frequent Visitor

Hello!
I can see that @donsvensen & @Vvelarde suggested easy approach and it seems to work in my report. Many thanks!
My measure is 

Sales LYSD = CALCULATE([BIG PROFIT],DATEADD('Calendar'[Date],-364,DAY))
AlexChen
Employee
Employee

Hi,

 

I want to know if your Tiempo[Fecha] is continuous date or not.

 

If it’s continuous date you can create a measure like below:

 

Measure yourMeasure = CALCULATE(SUM('Tiempo'[yourColumn]),SAMEPERIODLASTYEAR('Tiempo'[Fecha]))

 

When you use filter on this measure, remember to remove date hierarchy, just use date.

 

On the other way, if your Tiempo[Fecha] is not continuous date, there is not way to come to that result you want.

 

Best Regards

Alex

jpereztang
Helper I
Helper I

Hi @Vvelarde, yep you're right it was 3th July 2015 I confused it with August.

 

About your answer, It work for this year, but what happen if the year is a leap-year? It has 366 days and a normal year has 365. It have to be dynamic or something like that. My solution have to work to infinity and beyond

@jpereztang

 

One solution that came to mind is on the date (calendar) table

 

1. create a  column WeekNo= Weeknum([Calendar[FullDate])   ( Full Date is the Date column in DD/MM/YYYY format)

2. Create a column WeekDayNo = WeekDay(Calendar([FullDate])

3. Create a column Year = Year(Calendar[FullDate])

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

    depending on the Date.

5. Join your fact table to the Calendar table

6. Create a measure sya for Sales = sum(Facttable[SalesAmount])

7. Create a 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 ))

8. You should now be able to view the Sales and SalesLYDWY side by side.

Sample of what I did

 Column Labels             
 1 2 3 4 5 6 7 
Row LabelsSales2SWLYSales2SWLYSales2SWLYSales2SWLYSales2SWLYSales2SWLYSales2SWLY
2015              
24          41 43 
2545 47 49 51 53 55 57 
2659 61 63 65 67 69 71 
2773             
2015 Total177 108 112 116 120 165 171 
2016              
24           41 43
254145434745494751495351555357
265559576159636165636765696771
27697371 73         
2016 Total165 171 177 108 112 116 120 

 

This should work for all years and weeks for eternity.

 

If this solves your requirement please accept this as a solution and also give KUDOS.

 

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

Hello! I want to spilt the sales per store, I have 8 stores. I tried your solution but I get the sales for one store. Do you know why is this happening? Has to do with the max? Any solutions? Tnank you!

Anonymous
Not applicable

Hi @CheenuSing !

This solution is working perfectly, thank you very much. However, I need to restitute this second date "DWY". I can't achieve it. Do you know how to restitute the date in format "dd/mm/yyyy" ? 

 

 

Thanks by advance,

Anonymous
Not applicable

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

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

That bit on using the EARLIER function to get the right total when using this as a combined measure just made my month! I owe you big time!

Hi CheenuSingh,

 

Just wondering why you would multiply WeekDayNo by 100? Please could you explain?

Thank you

Vijay

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

This dax actually helps me  a  lot.. Now I can able to compare weekday with previous year same day. In my case I have to write a  dax for if both the years has same weekday in a month (sunday with sunday, monday with monday etc.,) then i'm supposed to assingn it as P, If any non comparable day in a month then it should asssign as "T". Also it will workout in the case of leap year.  Please help me with the  exact dax. TIA1.PNG2.PNG3.PNG

Anonymous
Not applicable

Hi, did you find a solution for your problem, I would be so happy if you did because I am struggling with the same problem! Thanks

Dear @CheenuSing

 

Thank you, this is great. 

 

The only challenge I have is that my last year based on your number. 7 has one drawback. 

 

Net Sales LY test = CALCULATE([Net Sales test];FILTER(ALL('TestDWH Date');'TestDWH Date'[DWY] = MAX('TestDWH Date'[DWY])-1))

 

When I have it on days, i.e. monday, tuesday etc, my "total" row in f.ex. a table only show value for sunday LY. Do I need any IF statement? 

 

Thank you,

Mikkel 

Hi @Mikkelnaes

 

Could you please post your pbix in one drive / google drive and share the link.

 

Please also explain what is not working correctly.

 

Cheers

 

CheenuSing

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

Proud to be a Datanaut!

Hi

 

A leep year still has 52 weeks so you will hit the same weekday.

 

But in years with 53 weeks you have to decide whether you want to go 364 days (52*7) back or (53*7) - the easy way is always to use 364 (52*7) 🙂

 

br

Erik

Vvelarde
Community Champion
Community Champion

hi @jpereztang

 

If today is July 1st 2016; A year ago was July 3th 2015.

 

 




Lima - Peru

the dax maybe was:

 

Calendar[Date] - 364

 




Lima - Peru

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.