cancel
Showing results 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]),
DATESBETWEEN('Date'[Date],FIRSTDATE('Date'[Date])-WEEKDAY(FIRSTDATE('Date'[Date]),1)+1,LASTDATE('Date'[Date])-WEEKDAY(LASTDATE('Date'[Date]),1)+7))
VAR Weekworkingdays = CALCULATE(
SUM('Date'[Working day]),
DATESBETWEEN('Date'[Date],FIRSTDATE('Date'[Date])-WEEKDAY(FIRSTDATE('Date'[Date]),1)+1,LASTDATE('Date'[Date])-WEEKDAY(LASTDATE('Date'[Date]),1)+7))
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.

4 REPLIES 4
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)``````

Proud to be a Super User!

Frequent Visitor

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

@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

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

Divide value by 7 in a new column

Proud to be a Super User!

Frequent Visitor

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

Announcements

#### Microsoft named a Leader in The Forrester Wave

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