cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarkCBB Member
Member

Last Year This/Same Week

Hello there,

 

I created the follow DAX Measure to calculate This Year This Week, I am sure there is a better way to do this, but this does work:

TYTW Units = CALCULATE([Total Units],FILTER('CALENDAR','CALENDAR'[Week Year ID]=MAXX('CALENDAR','CALENDAR'[Week Year ID])))

The Week Year ID, is not the Week index number of the year, but a rolling index from the 1st week of data, i.e. 2014-01-01 is week Year ID 1, and the Week Year ID for 2015-01-01 is 54 and 2017-01-01 is 162. thus my current Week Year iD (as of 2017-04-26) is 176.

 

but using that ID I am able to get the "This Week" sales, but I am not sure how to get the same timeframe for Last Year i.e. Last Year This Week.

 

any ideas on how to approach this?

 

PS, I am going to use this result in a card visual.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Last Year This/Same Week

How is [Week Year ID] stored?  Could you build last years [Week Year ID] by making use of the same one you used in your TWTY measure?

For Example if [Week Year ID] is YYYYWW you could build the string using a combination of LEFT and RIGHT to get the Year and Week components, then decrement the YYYY by one and build a new filter string from there.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


9 REPLIES 9
Super User
Super User

Re: Last Year This/Same Week

Would the dax formula SAMEPERIODLASTYEAR help?

https://msdn.microsoft.com/en-us/library/ee634972.aspx


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


kaushikd Member
Member

Re: Last Year This/Same Week

@MarkCBB

 

Hi there is a dax function that might help you.

SAMEPERIODLASTYEAR(<dates>) 

 

MarkCBB Member
Member

Re: Last Year This/Same Week

Hello @kaushikd and @Ross73312

 

I have used that before, but the way I have created the above measure does not make use any date range, how would I determine the to date range for this week this year, I am sure if I can get that date range, I could use the 

SAMEPERIODLASTYEAR(<dates>) 

 

 

Super User
Super User

Re: Last Year This/Same Week

How is [Week Year ID] stored?  Could you build last years [Week Year ID] by making use of the same one you used in your TWTY measure?

For Example if [Week Year ID] is YYYYWW you could build the string using a combination of LEFT and RIGHT to get the Year and Week components, then decrement the YYYY by one and build a new filter string from there.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


MarkCBB Member
Member

Re: Last Year This/Same Week

ooo, that is a clever approach.

It is stored as an INT (Whole number), download link below:

Calendar

 

That said, all I need to do is change the Year Week ID to the format you proposed and I should be able to implement that approach.

 

Going to try now, I have not tried to use LEFT/RIGHT into a Measure before, let me give it a go and I will post back.

 

Super User
Super User

Re: Last Year This/Same Week

Hi @MarkCBB

 

Try the following steps :

 

1. Create a MasterCalendar table from Minimum of date of FactTable to the Maximum Date of Factable using

    MasterCalendar = Calendar(Min(yourFactTable[Date]),max(yourFactTable[Date]))

2. This will create a table with a column called Date.

3. Create a column called Year = year(MasterCalendar[Date])

3. Create a column called WeekNum =WEEKNUM(MasterCalendar[Date])

   This will set the week number from 1 starting from Jan 1 for each year.

4. Create a column called WeekYear = [WeekNum] * 10000 + [Year]

    The out put will be like 12014, 22014,....,262017

5. Create a column called WeekLastYear = [WeekYear] -1

6. Create a measure called TotalUnits = sum(yourFactTable[Units])

7. Create a measure called ThisWeek = Weeknum(Today()) * 10000 + Year(Today())

8. Create a measure called ThisWeekSoldUnits = Calculate ([TotalUnits],Filter(ALL(MasterCalendar), [WeekYear] = [ThisWeek] ))

9. Create a measure called LastYearSameWeekSoldUnits = Calculate ([TotalUnits],Filter(ALL(MasterCalendar), [WeekLastYear] = [ThisWeek] - 1))

 

Now create your card visuals using ThisWeekSoldUnits and LastYearSameWeekSoldUnits .

 

If this works for you 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!
Highlighted
Moderator v-caliao-msft
Moderator

Re: Last Year This/Same Week

@MarkCBB,

 

Since there are no PREVIOUSWEEK function in DAX.

 

So if need to get last year same week, you need to calculate the Year and week and weektotal in your data model, and then use LOOKUPVALUE function to get previous week value and last year same week value.

I have two tables in my sample data model. In your date table, create two columns.
Year = YEAR('Date'[CalendarDate])
WeekNumber = WEEKNUM('Date'[CalendarDate])

then create a new table
Table = SUMMARIZE('Date','Date'[Year],'Date'[WeekNumber],"Weektotal",calculate(SUM(Sales[SalesAmount]),ALLEXCEPT('Date','Date'[Year],'Date'[WeekNumber])))
Create three measures.
WeekTotalSaleAmount = calculate(SUM(Sales[SalesAmount]),ALLEXCEPT('Date','Date'[Year],'Date'[WeekNumber]))
PreviousWeek = LOOKUPVALUE('Table'[Weektotal],'Table'[Year],IF(MAX('Date'[WeekNumber])=1,MAX('Date'[Year])-1,MAX('Date'[Year])),'Table'[WeekNumber],IF(MAX('date'[WeekNumber])=1,53,MAX('Date'[WeekNumber])-1))

PreviousYearWeek = LOOKUPVALUE('Table'[Weektotal],'Table'[Year],MAX('date'[Year])-1,'Table'[WeekNumber],MAX('date'[WeekNumber]))

 

Capture.PNG Capture1.PNG

 

Regards,

Charlie Liao

MarkCBB Member
Member

Re: Last Year This/Same Week

Hello @Ross73312

 

Your approach works well, I changed the measure seems to works well:

PYTW Units = CALCULATE([Total Units],FILTER(ALL('CALENDAR'),'CALENDAR'[Week Year ID]=MAXX('CALENDAR','CALENDAR'[Week Year ID]-100)))

-100 to get the same week previous week

 

Thank you

KPorterellis Frequent Visitor
Frequent Visitor

Re: Last Year This/Same Week

This solution works great, but I have a newbie question. How would you adapt it to add in a category. For example I would like the PreviousYearWeek to return the number of Bikes sold the same week last year and the number of balls. Right now it seems to total my groups by week.