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

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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

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

    T

     

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Icon for Microsoft Employee rankMicrosoft 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