Dynamic rolling charts

[tylerdv]

Hey All!

Im getting the hang of Power BI, but there is one crucial time saving thing i am missing

I used to use excel's 'Offset' function with a name range to create dynamic charts/graphs that would automatically add a weeks worth of data as it got added.

I've created a column in my data using the 'Endofweek' funtion, now i need a way for a visulisation to give me -6 weeks of data based on this.

Ive seen a lot of answers that go back 42 days, but im focusing on weeks, and this makes it a bit tricky

Any help would be much appreciated

Thanks

Tyler

[Phil_Seamark]

Please add the following calculated column to your date table

Weeks from today = IFERROR(DATEDIFF('Dates'[Date],NOW(),WEEK),-1)

Then you can drag this field into your Report, Page or Visual level filter and set to be between 0 and 6 (if you want to show the last rolling 6 weeks)

[v-ljerr-msft]

Hi @tylerdv,

I assume that you already have the Year and WeekNumber columns in your Calendar table. If not, you can use the formula below to add them first.

Year = YEAR ( 'CalendarTable'[Date] )

WeekNumber = WEEKNUM ( 'CalendarTable'[Date] )

Then you should be able to use the formula below to create a measure to calculate -6 weeks of sales, and show the measure on the chart with your Date column on the report.

Rolling 6 Week Sales =
VAR currentWeek =
    MAX ( 'CalendarTable'[WeekNumber] )
VAR currentYear =
    MAX ( 'CalendarTable'[Year] )
RETURN
    IF (
        currentWeek - 6
            < 1,
        CALCULATE (
            [Total Sales],
            FILTER (
                ALL ( CalendarTable ),
                'CalendarTable'[Year]
                    = currentYear - 1
                    && 'CalendarTable'[WeekNumber]
                    = 52 + currentWeek
                    - 6
            )
        ),
        CALCULATE (
            [Total Sales],
            FILTER (
                ALL ( CalendarTable ),
                'CalendarTable'[Year] = currentYear
                    && 'CalendarTable'[WeekNumber]
                    = currentWeek - 6
            )
        )
    )

Regards

[tylerdv]

Hi @v-ljerr-msft thanks for your response!

How would the bellow code work for it to create a flag that i can then filter on for a visual?

Thanks for you help

Thanks

T

[v-ljerr-msft · Accepted solution]

Hi @tylerdv,

I assume you have a table with a flag of weeks you want roll back like 'FlagTable'[Flag].

Then the formula to create the measure should like below.

Rolling n Week Sales =
VAR currentWeek =
    MAX ( 'CalendarTable'[WeekNumber] )
VAR currentYear =
    MAX ( 'CalendarTable'[Year] )
VAR selectedFlag =
    MAX ( 'FlagTable'[Flag] )
RETURN
    IF (
        currentWeek - selectedFlag
            < 1,
        CALCULATE (
            [Total Sales],
            FILTER (
                ALL ( CalendarTable ),
                'CalendarTable'[Year]
                    = currentYear - 1
                    && 'CalendarTable'[WeekNumber]
                    = 52 + currentWeek
                    - selectedFlag
            )
        ),
        CALCULATE (
            [Total Sales],
            FILTER (
                ALL ( CalendarTable ),
                'CalendarTable'[Year] = currentYear
                    && 'CalendarTable'[WeekNumber]
                    = currentWeek - selectedFlag
            )
        )
    )

Regard