sales the last 20 weekdays

[jayjay0306]

Hi experts,

I hoope you can help me here:

I need to calculate the sales for a product in the last 20 week days. My problem is to frame the timeperiod to 20 weekdays, not only last 20 days(dates).

Here is my problem:

I have this table (for simplicity, it is just showing all products):

As you can see I have tried to make a measure showing sales for the last 20 weekdays with this:

Sales 20 days_Sum = CALCULATE(SUM('KPI Sell In'[Sales In Packs CY]),'Calendar'[CALENDAR_WEEK_END_IND]>0,DATESBETWEEN('Calendar'[Date],TODAY()-21,TODAY()-1))

but in this way I only get the latest 20 days from today (12-06-2022 - 11-17-2022) which is only 14 weekdays. Result: 9.103.800

What I need is the full 20 weekdays (in this case the period: 12-06-2022 - 11-09-2022) Result: 11.961.019

The complexity is, that the number of weekdays changes depending on the day in week.

In short: How do I make a measure showing the sales for rolling 20 weekdays in this setup? 

Any ideas wil be be greatly appreciated.

thanks.

Br,

JayJay0306

[v-tangjie-msft · Accepted solution]

Hi @jayjay0306 ,

According to your description, here are my steps you can follow as a solution.

(1) We can create a calculated column.

Weekday = var a=WEEKDAY('Table'[Date],2)
return IF(a<=5,"weekday","weekend")

(2)We can create a measure. 

Measure = var a=FILTER(ALL('Table'),[Date]<TODAY()&&'Table'[Weekday]="weekday")
var b=TOPN(20,a,[Date],DESC)
return SUMX(b,[Value])

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[jayjay0306]

Thanks Neeko, now I can see what you are getting at, and it works. 🙂

Br,

jayjay0306

[jayjay0306]

Thanks Neeko Tang,

We are close. However, it seem like it doesn't work as intended:

First of all, your "weekday"- calculation, I already have defined in the table (i.e. "WEEKEND_IND"), therefore I use this instead.

Hence, "your" calculation look like this:

_Measure = 
var a=FILTER(ALL('Calendar'),'Calendar'[Date]&&'Calendar'[CALENDAR_WEEK_END_IND]=1)
var b=TOPN(20,a,'Calendar'[Date],DESC)
return 
SUMX(b,SUM('KPI Sell In'[Sales In Packs CY]))

However, the result doesn't show the sum of sales for the last 20 days. It simply multiplies the sales each day with the number of days shown in TOPN() ?

example:

in the following I only pick one date (i.e. TOPN(1,....): 

_Measure = 
var a=FILTER(ALL('Calendar'),'Calendar'[Date]&&'Calendar'[WEEKEND_IND]=1)
var b=TOPN(1,a,'Calendar'[Date],DESC)
return 
SUMX(b,SUM('Sales'[Sales]))

Result:

If we look at fx.the date "12-06-2022" we get the same sales amount on this date = 736.430.

This is fine.

But if I change the calcualtion to 2 days (i.e. TOPN(2,.....):

_Measure = 
var a=FILTER(ALL('Calendar'),'Calendar'[Date]&&'Calendar'[WEEKEND_IND]=1)
var b=TOPN(2,a,'Calendar'[Date],DESC)
return 
SUMX(b,SUM('Sales'[Sales]))

Result:

...I don't get the expected: 736.430+507368 = 1.243.798,

instead I get: 2 x 736.430 = 1.472.860 ?

Do you know why?

Br,

Jayjay0306

[v-tangjie-msft]

Hi @jayjay0306 ,

Please create a calculated column in the sales table.

CALENDAR_WEEK_END_IND = var a=WEEKDAY('Sales'[Date],2)
return IF(a<=5,1,0)

Then create a measure in the sales table.

Measure = var a=FILTER(ALL('Sales'),[Date]<=SELECTEDVALUE('Sales'[Date])&&'Sales'[CALENDAR_WEEK_END_IND]=1)
var b=TOPN(2,a,[Date],DESC)
return SUMX(b,Sales[Sales])

Then the result is as follows.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[jayjay0306]

Thanks Greg, are you takling about making a calculated table? I am not sure if I understand all what you're saying? Could you please show me some of the way?

thanks.

br,

jayjay0306

[Greg_Deckler]

@jayjay0306 Generally you do something like filter for the last 28 days but then filter that for WEEKDAY([Date],2) < 6 and then you could ADDCOLUMNS a simple counter for rows where the date is greater than or equal to the currrent date and then filter that down for this counter <= 20. Generally easier if you use a SUMX approach instead of CALCULATE.