Creating a custom weekly summary

[HersonD]

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!

[Nasif_Azam · Accepted solution]

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 · Accepted solution]

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]

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

[Nasif_Azam · Accepted solution]

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

[Irwan]

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.