Forum Discussion

Jayaselvan's avatar
Jayaselvan
Helper II
4 years ago
Solved

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 ...
Need Help
  • parry2k's avatar
    parry2k
    4 years ago

    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!

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

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.