Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Weekly sales to daily and then monthly

Hi everyone,


I have a weekly sales data which needs to be translated into monthly data. The tricky part is some weeks belong to two months which I would like to split the data by working day.


The original data looks somewhat like this:



I have connected that to a Date table. So the calculation should be like this:

For 27 December 2020, 4/5 sales goes to December and 1/5 Sales goes to January

For 17, 24 January 2021, all sales goes to January

For 31 January 2021, all sales goes to February as 31 January is a Sunday


I wrote something like this:


Dailyspread NetSales LC = 
VAR Totalweekly = CALCULATE(
    SUM('Covid Report'[Net Sales LC]),
VAR Weekworkingdays = CALCULATE(
    SUM('Date'[Working day]),
RETURN SUMX('Date',DIVIDE(Totalweekly,Weekworkingdays,0)*'Date'[Working day])


The result I got is correct on Daily and Weekly Level but totally wrong on Monthly Level



I am stuck. Could someone please show me the way? Thank you so much in advance.


Super User II
Super User II


there is a workaround, you create two columns in datetime table

month = month('Table 2'[Date])

Column = 
VAR _date=maxx(FILTER('Table','Table'[Date]<'Table 2'[Date]),'Table'[Date])
return if(WEEKDAY('Table 2'[Date],1) in {1,7},0,maxx(FILTER('Table','Table'[Date]=_date),'Table'[Sales])/5)

please see the attachment below

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Thanks for your reply but it doesn't seem to work for me




Please also note that in my fact table, one date can contain many transactions.


Super User IV
Super User IV

@annguyenjoh , One of the thing which you can so create a date table in power query and add a weekend column

Try like, calendar, I think I added week start code


Merge this with your table. and divide number by 7 , you will get daily data. join on week start or week end


You can get calendar in DAX


Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)


merge two using natutal join or cross join with filter on week start or week end


Divide value by 7 in a new column







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thanks for the reply. I think the tricky part here is I only want to divide by working days.

Helpful resources

PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.