Forum Discussion
Daily Data to Weekly Data by Aggregation
I have daily Sales Quantity Data. I need to Summarize the table on weekly Level.
The weekly summary table should have date(Wednesday) and Aggregated Sales Quantity(from Thur to Wed) Columns.
Input Table (Daily Data):
Expected Output Table(Weekly Data):
Please let me know If I am not clear.
Thanks in advance!
Jayaselvan actually it is pretty straight forward, first you need to add a calendar dimension in your model, as a best practice when you are working with dates, always use a calendar dimension.
In this new calendar dimension table, add a following column:
Week Date = VAR __dow = WEEKDAY ( 'Calendar'[Date], 3) VAR __days = 2 - __dow VAR __newDays = if ( __days < 0, 9 - __dow, __days ) RETURN 'Calendar'[Date] + __newDaysSet relationship between this new calendar table with the transaction table, which will be one to many relationship.
To visualize, use table visual, drop week date column from calendar dimension and sum sales qty from transaction table, and you will see the sum by each week starting from Thur - Wed.
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
4 Replies
Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Thanks fot the tips. Will follow the same in future.
Jayaselvan actually it is pretty straight forward, first you need to add a calendar dimension in your model, as a best practice when you are working with dates, always use a calendar dimension.
In this new calendar dimension table, add a following column:
Week Date = VAR __dow = WEEKDAY ( 'Calendar'[Date], 3) VAR __days = 2 - __dow VAR __newDays = if ( __days < 0, 9 - __dow, __days ) RETURN 'Calendar'[Date] + __newDaysSet relationship between this new calendar table with the transaction table, which will be one to many relationship.
To visualize, use table visual, drop week date column from calendar dimension and sum sales qty from transaction table, and you will see the sum by each week starting from Thur - Wed.
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
Hi,
Create a Calendar Table with a calculated column formula for Weekday. Create a relationship (Many to One and Sngle) from the Date column of your Data Table to the Date column of the Calendar Table. To your visual, drag Date and Weekday from the Calendar Table. Write these measures
Qty = sum(Data[Sales Qty])
Qty for week ended = if(calendar[weekday]="Wednesday",calculate([Qty],datesbetween(calendar[Date],min(calendar[date])-6,min(calendar[date]))),blank())
Hope this helps.
