Forum Discussion
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.
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.