cancel
Showing results for
Did you mean:
Helper III

calculate sales between 2 periodos

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.

Thanks

1 ACCEPTED SOLUTION
Helper III

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
7 REPLIES 7
Community Support

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

Helper III

Hi.

You will find the 3 ways I tried to sort out this issue, each one in a different table. However none works properly.

Thanks

Super User IV

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.

Proud to be a Super User!

Super User IV

Have you looked at WEEKNUM?

---------------------------------------

I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Helper III

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.

Helper III

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
Helper III

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

Announcements