Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi community!
I am trying to reach the last week sales amount and split them between Monday to Sunday.
I tried 2 different ways but I have also got the same problem, I was able to get only the total week sales:
day_week_name_ | sales-1w |
Monday | 8390 |
Tuesday | 8390 |
Wednesday | 8390 |
Thursday | 8390 |
Friday | 8390 |
Saturday | 8390 |
Sunday | 8390 |
My first tried was to create a DATESINPERIOD measure:
sales-1w = CALCULATE([orders_number]; DATESINPERIOD(Calendario_pedido[Order_day];TODAY()-1;-7;DAY))
As I got the above issue, I have also attempted to create 2 new columns:
Last_Monday = Calendario_pedido[Order_day] - WEEKDAY(Calendario_pedido[Order_day];2) -6
Last_Sunday = LASTDATE ( Calendario_pedido[Order_day] ) - WEEKDAY ( LASTDATE (Calendario_pedido[Order_day]); 2 )
And with the measure formula:
sales-1w = CALCULATE([sales];
DATESBETWEEN (Calendario_pedido[Order_day];
MIN(Calendario_pedido[Last_Monday]);
MAX(Calendario_pedido[Last_Sunday ])))
As you may see, any of these 2 ways let me split the total sales amount (€8390) within Monday to Sunday.
May anyone help me please?
Thanks
Solved! Go to Solution.
I got the solution myself.
First of all I created a new column in the calendar:
Hi @alexrf86 ,
Please share me a dummy PBIX file, in which the sample data has the same data structure as your real data. Please remove sensitive information. And then, I will conduct specific tests.
Best Regards,
Icey
Hi.
You can download a dummy file here:
https://drive.google.com/open?id=1HSNCfpCy-YGdZ_9zoQs9A6cm2npmOeSP
You will find the 3 ways I tried to sort out this issue, each one in a different table. However none works properly.
https://drive.google.com/open?id=1HSNCfpCy-YGdZ_9zoQs9A6cm2npmOeSP
Thanks
As Suggested by @Greg_Deckler , use weeknum with option 2, that will give you week Monday to Sunday
weeknum(date[date],2)
Refer to this file: https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0
Using Rank to get last week.
Have you looked at WEEKNUM?
Weeknum as filter does not let me to choose the last 3 weeks, so every week I should edit the filter.
Week Rank does not split weeks properly as it takes a week as the last 7 days instead of natural weeks.
I got the solution myself.
First of all I created a new column in the calendar:
I need 4 weekly columns splitting the sales within Monday to Sunday: current week, last week, 2 weeks ago and 3 weeks ago.
It should be something like this:
weekday | Current Week | Last Week | 2 weeks ago |
Monday | 12 | 24 | 22 |
Tuesday | 25 | 55 | 31 |
Wednesday | 33 | 43 | 44 |
Thursday | 55 | 32 | |
Friday | 54 | 33 | |
Saturday | 22 | 41 | |
Sunday | 12 | 23 |
User | Count |
---|---|
94 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |