Forum Discussion
Rolling 52 weeks.
Hi all,
I want to create this visual in PBI for our [sales] measure (pretend that w.2 is last week and for 52 weeks.
So, one line to show the sales rolling 52 weeks and one to show the same period the year before.
Can you please advise? I have a week column in my date table which is a whole number and a YearWeek column which is stored as text. No changes can be made to the formats and I am not allowed to create any new columns.
Thanks!
7 Replies
- bhanu_gautam
Super User
mrclay823 , Create Measures for Rolling 52 Weeks Sales:
Rolling52WeeksSales =
CALCULATE(
SUM(Sales[SalesAmount]),
DATESINPERIOD(
DateTable[Date],
MAX(DateTable[Date]),
-52,
WEEK
)
)Create Measures for the Same Period Last Year:
Rolling52WeeksSalesLastYear =
CALCULATE(
[Rolling52WeeksSales],
SAMEPERIODLASTYEAR(DateTable[Date])
)Add the DateTable[Date] to the axis.
Add the Rolling52WeeksSales measure to the values.
Add the Rolling52WeeksSalesLastYear measure to the values.Hi,
- techies
Hi there mrclay823
Have you tried this?
Rolling 52 Weeks Sales =CALCULATE([sales],FILTER(ALLSELECTED('date'),YEAR('date'[Date]) = YEAR(MAX('date'[Date])) &&'date'[Weeknum] <= WEEKNUM(MAX('date'[Date])) &&'date'[Weeknum] > WEEKNUM(MAX('date'[Date])) - 52),ALLEXCEPT('date', 'date'[Year]))
- Poojara_D12
Hi mrclay823
To create this rolling 52-week comparison visual in Power BI with one line for the last 52 weeks and another for the same period the previous year, you can use DAX measures. Since you cannot modify column formats or create new columns, we will work with measures only.
This measure will sum the sales for the last 52 weeks dynamically:
Sales Rolling 52 Weeks = VAR MaxWeek = MAX('Date'[Week]) -- Get the latest selected week VAR MaxYearWeek = MAX('Date'[YearWeek]) -- Get the latest YearWeek in context RETURN CALCULATE( SUM(Sales[SalesAmount]), 'Date'[YearWeek] <= MaxYearWeek && 'Date'[YearWeek] > MaxYearWeek - 52 -- Include the last 52 weeks )To compare the same period from the previous year, shift the YearWeek by 52 weeks back:
Sales Rolling 52 Weeks LY = VAR MaxWeek = MAX('Date'[Week]) VAR MaxYearWeek = MAX('Date'[YearWeek]) RETURN CALCULATE( SUM(Sales[SalesAmount]), 'Date'[YearWeek] <= MaxYearWeek - 52 && 'Date'[YearWeek] > MaxYearWeek - 104 -- Shift 52 weeks back ) - techiesRolling 52 Weeks Sales =CALCULATE([sales],DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-365,DAY),VALUES('Date'[Weeknum]))
