Dynamic rolling charts

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

Need Help

v-ljerr-msft

9 years ago

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.:smileyhappy:

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

tylerdv

Frequent Visitor

9 years ago

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

v-ljerr-msft

Microsoft Employee

9 years ago

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.:smileyhappy:

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

Forum Discussion

Dynamic rolling charts