Daily Data to Weekly Data by Aggregation

[Jayaselvan]

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!

[Ashish_Mathur]

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.

[parry2k · Accepted solution]

@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] + __newDays 

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

[lbendlin]

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

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

[Jayaselvan]

Thanks fot the tips. Will follow the same in future.