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

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

4 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft 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)

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