topic Re: Rolling 52 weeks. in DAX Commands and Tips

Rolling 52 weeks.

mrclay823 — Fri, 31 Jan 2025 12:12:26 GMT

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!

Re: Rolling 52 weeks.

bhanu_gautam — Fri, 31 Jan 2025 14:23:03 GMT

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.

Re: Rolling 52 weeks.

Poojara_D12 — Sat, 01 Feb 2025 04:58:26 GMT

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 )

Re: Rolling 52 weeks.

techies — Sat, 01 Feb 2025 17:04:33 GMT

Rolling 52 Weeks Sales =

CALCULATE(

[sales],

DATESINPERIOD(

'Date'[Date],

MAX('Date'[Date]),

-365,

DAY

VALUES('Date'[Weeknum])

)

Re: Rolling 52 weeks.

techies — Sat, 01 Feb 2025 17:07:42 GMT

Hi mrclay823 Rolling 52 Weeks Sales =

CALCULATE(

[sales],

DATESINPERIOD(

'Date'[Date],

MAX('Date'[Date]),

-365,

DAY

VALUES('Date'[Weeknum])

)

Re: Rolling 52 weeks.

mrclay823 — Mon, 03 Feb 2025 09:12:59 GMT

Hey Poojara_D12 and thanks!

I am stuck on the first measure:

Re: Rolling 52 weeks.

mrclay823 — Mon, 03 Feb 2025 09:17:04 GMT

Hi,

Re: Rolling 52 weeks.

techies — Sun, 09 Feb 2025 08:22:01 GMT

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

)