Weekly sales to daily and then monthly

[Anonymous]

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.

[ryan_mayu]

@Anonymous 

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

[Anonymous]

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.

[amitchandak]

@Anonymous , 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

https://www.youtube.com/watch?v=so_A22HXbwM&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=105

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

https://www.youtube.com/watch?v=Qt0TM-4H09U&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=5

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

[Anonymous]

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