Forum Discussion
Weekly data into monthly format
Hi all,
I want to summarise Weekly data into Month format.
I do not have Day/Date level information in the table. Data only comes in Weekly format so having a calendar table might not solve the issue.
JULY 2020 Calendar:
| WEEK | Mon | Tue | Wed | Thu | Fri | Sat | Sun |
| 27 | 29 | 30 | 1 | 2 | 3 | 4 | 5 |
| 28 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
| 29 | 13 | 14 | 15 | 16 | 17 | 18 | 19 |
| 30 | 20 | 21 | 22 | 23 | 24 | 25 | 26 |
| 31 | 27 | 28 | 29 | 30 | 31 | 1 | 2 |
Week 27 and 31 spread into two months.
I want to sum [SALES] numbers for July but ignoring Non-July days (in gray). I want to apply this to all the months in any given year.
Eg data:
| NOTE | Week | Sales |
| Source data has no date field | 27 | 100 |
| Source data has no date field | 27 | 200 |
| Source data has no date field | 27 | 200 |
| Source data has no date field | 30 | 1000 |
| Source data has no date field | 30 | 1000 |
| Source data has no date field | 31 | 50 |
| Source data has no date field | 31 | 50 |
| Source data has no date field | 31 | 100 |
Output:
| Week | WeekSales Sum | Sales Sum * (5/7 days of week) | Result |
| 27 | 500 | 357.1 | Week 27 has 5 days in July and 2 in June so Sales sum for JULY should only incl 5 days. I guess in absence of DATE field in the source data, the percentage of 5days (5/7 days of a week) can be used to multiple by Sales to complete montly sum |
| 30 | 2000 | 2000 | Entire week is in July |
| 31 | 200 | 142.9 | 5 days in July and 2 in Aug so Sales sum for JULY should only incl 5 days |
Please let me know if this can be achieved using DAX.
Thanks in advance.
Hi, mb0307
You may check if the sample file helps.
If it meets your requirement,please follow these step:
1.create calcualted column in original table:
Sum sales this week = CALCULATE(SUM('Original Table'[Sales]),ALLEXCEPT('Original Table','Original Table'[Year],'Original Table'[Week]))Average daily sales this week = 'Original Table'[Sum sales this week]/72.Then create a new table with calendar date
New table with Calendar = CALENDAR(DATE(2019,01,01),DATE(2020,12,30))Add new calculated columns:
WeekNum = WEEKNUM('New table with Calendar'[Date],2)average daily sales = var a =CALCULATE(MAX('Original Table'[Average daily sales this week]),FILTER('Original Table','Original Table'[Week]='New table with Calendar'[WeekNum])) return IF(ISBLANK(a),0,a)Apply the filelds to table visualization and the result will show as below:
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Not sure I understand the full problem - what does the data look like in raw form? You have put a calendar in your post, does the data come in calendar format with Sales instead of the date?
You will likely still need a DimDate table: https://allisonkennedycv.blogspot.com/2020/04/dimdate-what-why-and-how.html
I would likely do the transformation of the data in Power Query Editor and not jsut DAX, but need to know a few more details.
AllisonKennedy Please see my original pot. I have added more info. Thank you.
amitchandak I will try your method. But first formula throwing this error: Too few arguments were passed to the DATE function. The minimum argument count for the function is 3.
Hi, mb0307
You may check if the sample file helps.
If it meets your requirement,please follow these step:
1.create calcualted column in original table:
Sum sales this week = CALCULATE(SUM('Original Table'[Sales]),ALLEXCEPT('Original Table','Original Table'[Year],'Original Table'[Week]))Average daily sales this week = 'Original Table'[Sum sales this week]/72.Then create a new table with calendar date
New table with Calendar = CALENDAR(DATE(2019,01,01),DATE(2020,12,30))Add new calculated columns:
WeekNum = WEEKNUM('New table with Calendar'[Date],2)average daily sales = var a =CALCULATE(MAX('Original Table'[Average daily sales this week]),FILTER('Original Table','Original Table'[Week]='New table with Calendar'[WeekNum])) return IF(ISBLANK(a),0,a)Apply the filelds to table visualization and the result will show as below:
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mb0307 , from week you can get week start date like this
date = date([year],[Week No]*7 -Weekday(date([year],1,1)))
This was with format YYYY WW
Weektodate = date(RIGHT(Table[Week Format],4),1,1)+(mid(Table[Week Format],5,SEARCH(" ",Table[Week Format])-5 )*7) - (WEEKDAY(date(RIGHT(Table[Week Format],4),1,1)))
once you have a date you can manage with date dimension. Across month you need split data
