Forum Discussion
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.
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.
10 Replies
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
Works perfectly fine for me. Many thanks.
Would the dax formula SAMEPERIODLASTYEAR help?
https://msdn.microsoft.com/en-us/library/ee634972.aspxHow 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.
