calculate sales between 2 periodos

[alexrf86]

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

[Icey]

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

[alexrf86]

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

[amitchandak]

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.

[Greg_Deckler]

Have you looked at WEEKNUM?

[alexrf86]

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.

[alexrf86 · Accepted solution]

I got the solution myself.

First of all I created a new column in the calendar: 

week_order = WEEKNUM(TODAY())-Calendario_pedido[Week_num_order]

Basically it gives me the week number based on the current week.

Then I have just needed to create the measures:

current_week_sales= CALCULATE([sales]; Calendario_pedido[week_order] = 0)

last_week_sales= CALCULATE([sales]; Calendario_pedido[week_order] = 1)

etc etc

[alexrf86]

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