Weekly data into monthly format

[mb0307]

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.

[amitchandak]

@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

[AllisonKennedy]

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.

[mb0307]

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

[v-easonf-msft · Accepted solution]

Hi, @mb0307 

You may check if the sample file helps.

pbix. attached

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

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