Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
@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
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.
@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
Thanks for the reply. I think the tricky part here is I only want to divide by working days.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |