cancel
Showing results for
Did you mean:
Regular Visitor

## 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]
)```

I hope you can help me

12 REPLIES 12
Super User

## Re: Weekday Last Year Comparison

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

Lima - Peru

Proud to be a Datanaut!

Super User

## Re: Weekday Last Year Comparison

the dax maybe was:

Calendar[Date] - 364

Lima - Peru

Proud to be a Datanaut!

Regular Visitor

## Re: Weekday Last Year Comparison

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

Member

## Re: Weekday Last Year Comparison

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

Member

## Re: Weekday Last Year Comparison

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

Super User

## Re: Weekday Last Year Comparison

@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 Labels Sales2 SWLY Sales2 SWLY Sales2 SWLY Sales2 SWLY Sales2 SWLY Sales2 SWLY Sales2 SWLY 2015 24 41 43 25 45 47 49 51 53 55 57 26 59 61 63 65 67 69 71 27 73 2015 Total 177 108 112 116 120 165 171 2016 24 41 43 25 41 45 43 47 45 49 47 51 49 53 51 55 53 57 26 55 59 57 61 59 63 61 65 63 67 65 69 67 71 27 69 73 71 73 2016 Total 165 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!
Frequent Visitor

## Re: Weekday Last Year Comparison

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

Super User

## Re: Weekday Last Year Comparison

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!
Frequent Visitor

## Re: Weekday Last Year Comparison

Hi CheenuSingh,

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

Thank you

Vijay