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.

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.

Top Solution Authors
Top Kudoed Authors