Forum Discussion

HersonD's avatar
HersonD
Helper I
1 year ago
Solved

Creating a custom weekly summary

Hi Fabric Community,

 

I want to create a weekly summary which I will use for my column chart.

 

I have done annual and monthly already but I don't know how to make a weekly summary.

My idea is to group dates from monday-sunday, labeling each week by the date of the sunday of that week. 

 

Also, my data set is not 1 day = 1 row, there could be multiple rows that has similar dates so I think I need to sum it by date as weel before grouping it to weekly.

 

Your help will be highly appreciated.

 

Thank you so much!

 

Need Help
  • Nasif_Azam's avatar
    Nasif_Azam
    1 year ago

    Hey HersonD ,

    To create a weekly summary for your column chart in Power BI (or another tool), grouped Monday to Sunday and labeled by the Sunday's date, here’s a step-by-step guide that accounts for your scenario where multiple rows can share the same date, and you need to sum before grouping by week.

    Step 1: Aggregate by Date

    DAX Example:

    DailySummary =
SUMMARIZE(
    YourTable,
    YourTable[Date],
    "DailyTotal", SUM(YourTable[Amount])
)

     

    Step 2: Assign Each Date to a Week Ending Sunday

    WeeklySummary =
ADDCOLUMNS(
    DailySummary,
    "WeekEnding",
    YourTable[Date] + (7 - WEEKDAY(YourTable[Date], 2))
)

    WEEKDAY(YourTable[Date], 2) considers Monday as day 1, Sunday as 7. This shifts the date forward to the upcoming Sunday.

    Step 3: Group by WeekEnding and Sum

    FinalWeeklySummary =
SUMMARIZE(
    WeeklySummary,
    [WeekEnding],
    "WeeklyTotal", SUM([DailyTotal])
)

    Step 4: Use in a Chart

    • Use WeekEnding as your X-axis in a column chart.

    • Use WeeklyTotal as your Y-axis value.

     

    If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.


    Best Regards,
    Nasif Azam

  • Akash_Varuna's avatar
    Akash_Varuna
    1 year ago

    Hi HersonD For this add a calculated column for the week number using WEEKNUM([YourDateColumn], 2) and another column for the week's Sunday using [YourDateColumn] + (7 - WEEKDAY([YourDateColumn], 2)). Create a measure to sum your values by date and group them by the Sunday date: SUM([YourValueColumn]). Use the WeekEnding column on the X-axis and WeeklyTotal measure on the Y-axis in a column chart, ensuring chronological sorting by WeekEnding.

4 Replies

  • Irwan's avatar
    Irwan
    Super User
    1 year ago

    hello HersonD 

     

    i think you need to define the "weekly" value as you described above.

    then plot it like you did in annual and monthly.

     

    multiple value can be done in visualization. for example, in bar chart, all value will be calculated together when they are under same "weekly" value. you can choose how the data is calculated either sum, average, count, etc.

     

    Thank you.

  • Nasif_Azam's avatar
    Nasif_Azam
    Super User
    1 year ago

    Hey HersonD ,

    To create a weekly summary for your column chart in Power BI (or another tool), grouped Monday to Sunday and labeled by the Sunday's date, here’s a step-by-step guide that accounts for your scenario where multiple rows can share the same date, and you need to sum before grouping by week.

    Step 1: Aggregate by Date

    DAX Example:

    DailySummary =
SUMMARIZE(
    YourTable,
    YourTable[Date],
    "DailyTotal", SUM(YourTable[Amount])
)

     

    Step 2: Assign Each Date to a Week Ending Sunday

    WeeklySummary =
ADDCOLUMNS(
    DailySummary,
    "WeekEnding",
    YourTable[Date] + (7 - WEEKDAY(YourTable[Date], 2))
)

    WEEKDAY(YourTable[Date], 2) considers Monday as day 1, Sunday as 7. This shifts the date forward to the upcoming Sunday.

    Step 3: Group by WeekEnding and Sum

    FinalWeeklySummary =
SUMMARIZE(
    WeeklySummary,
    [WeekEnding],
    "WeeklyTotal", SUM([DailyTotal])
)

    Step 4: Use in a Chart

    • Use WeekEnding as your X-axis in a column chart.

    • Use WeeklyTotal as your Y-axis value.

     

    If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.


    Best Regards,
    Nasif Azam

  • Akash_Varuna's avatar
    Akash_Varuna
    Super User
    1 year ago

    Hi HersonD For this add a calculated column for the week number using WEEKNUM([YourDateColumn], 2) and another column for the week's Sunday using [YourDateColumn] + (7 - WEEKDAY([YourDateColumn], 2)). Create a measure to sum your values by date and group them by the Sunday date: SUM([YourValueColumn]). Use the WeekEnding column on the X-axis and WeeklyTotal measure on the Y-axis in a column chart, ensuring chronological sorting by WeekEnding.

    • HersonD's avatar
      HersonD
      Helper I
      1 year ago

      Hi Akash_Varuna ,

       

      Thank you for this.

       

      I already solved it myself. 

      Your solution is almost similar to what I've done. 

      The only the difference is I did the grouping by sunday in Power Query.

       

      But this is a great way as well.

       

      Thank you