Rolling 52 weeks.

[mrclay823]

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!

[techies]

Hi @mrclay823 Rolling 52 Weeks Sales =

CALCULATE(

    [sales],  

    DATESINPERIOD(

        'Date'[Date],

        MAX('Date'[Date]),

        -365,  

        DAY

    ),

    VALUES('Date'[Weeknum])  

)

[techies]

Rolling 52 Weeks Sales =

CALCULATE(

    [sales],  

    DATESINPERIOD(

        'Date'[Date],

        MAX('Date'[Date]),

        -365,  

        DAY

    ),

    VALUES('Date'[Weeknum])  

)

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

[mrclay823]

Hey @Poojara_D12 and thanks!

I am stuck on the first measure:

[bhanu_gautam]

@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.

[mrclay823]

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

)