Forum Discussion

VK_nz's avatar
VK_nz
Icon for Helper I rankHelper I
2 years ago
Solved

Ploting rolling last four weeks

Hi all,

 

Appreciate pointer in right dirction, I am converting Excel dashboard to PowerBI.

How do I plot rolling last 4 weeks (week Monday to Sunday)? below table I had designed with Pivot tbl date grouping.

In my PB modeling I got Fiscal year calendar table. 

 

As data is refreshed weekly, this need to be rolling last four week from current week.

 

Thank you for your time.

 

 

    
Sales WeekItem1Item2Grand Total
05/08/2024 - 11/08/2024459
12/08/2024 - 18/08/20249413
19/08/2024 - 25/08/20245712
26/08/2024 - 01/09/2024268
Grand Total202242
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    VK_nz , First create a date table 

     

    DateTable =
    VAR BaseCalendar =
    CALENDAR(
    DATE(2020, 1, 1),
    DATE(2030, 12, 31)
    )
    VAR AddColumns =
    ADDCOLUMNS(
    BaseCalendar,
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "Day", DAY([Date]),
    "Weekday", WEEKDAY([Date], 2), // 2 makes Monday the first day of the week
    "Week Number", WEEKNUM([Date], 2), // 2 makes Monday the first day of the week
    "Month Name", FORMAT([Date], "MMMM"),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "Year-Month", FORMAT([Date], "YYYY-MM")
    )
    RETURN
    AddColumns

     

    Then create a relationship between date table and your table and then create a measure for rolling 4 weeks

    Rolling4WeeksSales =
    CALCULATE(
    SUM(Sales[SalesAmount]),
    DATESINPERIOD(
    'Date'[Date],
    MAX('Date'[Date]),
    -4,
    WEEK
    )
    )

     

    Use the Date table to filter the data to show only the last 4 weeks.
    You can create a slicer or use a visual level filter to ensure only the last 4 weeks are displayed.

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi VK_nz ， hello bhanu_gautam and shafiz_p，thank you for your prompt reply!

     

    What did you mean unable to sort by weekno column?

     

    If you want to sort one visual by specified column, add the column to the visual first, then select Sort by of the visual to sort the visual as you need:

    If you want the sort order apply to any visual in the report that contains the sorted column, we could go to the table view, sort one column by another column:

    Best regards,

    Joyce

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

     

6 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    VK_nz , First create a date table 

     

    DateTable =
    VAR BaseCalendar =
    CALENDAR(
    DATE(2020, 1, 1),
    DATE(2030, 12, 31)
    )
    VAR AddColumns =
    ADDCOLUMNS(
    BaseCalendar,
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "Day", DAY([Date]),
    "Weekday", WEEKDAY([Date], 2), // 2 makes Monday the first day of the week
    "Week Number", WEEKNUM([Date], 2), // 2 makes Monday the first day of the week
    "Month Name", FORMAT([Date], "MMMM"),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "Year-Month", FORMAT([Date], "YYYY-MM")
    )
    RETURN
    AddColumns

     

    Then create a relationship between date table and your table and then create a measure for rolling 4 weeks

    Rolling4WeeksSales =
    CALCULATE(
    SUM(Sales[SalesAmount]),
    DATESINPERIOD(
    'Date'[Date],
    MAX('Date'[Date]),
    -4,
    WEEK
    )
    )

     

    Use the Date table to filter the data to show only the last 4 weeks.
    You can create a slicer or use a visual level filter to ensure only the last 4 weeks are displayed.

  • shafiz_p's avatar
    shafiz_p
    Icon for Super User rankSuper User
    2 years ago

    Hi VK_nz  To calculate Rolling total last 4 week, use the below calculated column formula :

     

    First create a weeknumber column using power query.

    Hope this helps!!
    If this solved your problem, accept it as a solution!!

    Best Regards,
    Shahariar Hafiz

    • VK_nz's avatar
      VK_nz
      Icon for Helper I rankHelper I
      2 years ago

      shafiz_p  thank you, still got issue with week sorting, already tried adding weekNo. 

      also is there way to show last four week from current week bydefault or use need to select week everytime?

       

      Thanks for your help.

  • VK_nz's avatar
    VK_nz
    Icon for Helper I rankHelper I
    2 years ago

     bhanu_gautam Thank you for the solution, I'll apply and let you know how it goes.

    just a side note, is there way to display last four week in table, graph by default rather than applying filter every time?

     

    • VK_nz's avatar
      VK_nz
      Icon for Helper I rankHelper I
      2 years ago

      bhanu_gautam it worked exactly as i wanted, thank you. 

      How do I sort the week? to show filter value as week1,2,3,.. right now its showing week1, week10,11

       

      I tried with addng week no. 

      FinWeekNo =
              WEEKNUM(EDATE(FinCal[Date],-6),2)
       
      but it doesnt allow to sort by week no. column.

      Thank you

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi VK_nz ， hello bhanu_gautam and shafiz_p，thank you for your prompt reply!

         

        What did you mean unable to sort by weekno column?

         

        If you want to sort one visual by specified column, add the column to the visual first, then select Sort by of the visual to sort the visual as you need:

        If you want the sort order apply to any visual in the report that contains the sorted column, we could go to the table view, sort one column by another column:

        Best regards,

        Joyce

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.