Forum Discussion

tylerdv's avatar
tylerdv
Frequent Visitor
9 years ago
Solved

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's avatar
    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

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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)