cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
annguyenjoh
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:

annguyenjoh_0-1619702800974.png

 

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

annguyenjoh_1-1619702980674.png

 

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

 

4 REPLIES 4
ryan_mayu
Super User II
Super User II

@annguyenjoh 

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

 

annguyenjoh_0-1619722267001.png

 

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

 

amitchandak
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

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

 

 

 

 

 

 



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

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors