Help with New Measure - Top N and Related Table

[Jenni-Sky]

Hi, 

I have two tables: 

Date

Date	Working Day
14 May 2020	True
13 May 2020	True
12 May 2020	True
11 May 2020	True
10 May 2020	False
9 May 2020	False
8 May 2020	False
...	...

Orders Rec

Order	Date	GBP
1	11 May 2020	10
2	1 Jan 2020	15
3	14 May 2020	5

I need to create a dynanmic measure which calculates the Sum of GBP for all orders which have been placed in the past 20 WORKING DAYS (not including today). 

I think I need to use TOPN somehow on the dates table, possibly in a similar way to the below: 

TOPN(20,FILTER('Date','Date'[Date]<>TODAY() && 'Date'[Working Day]=TRUE()),'Date'[Date],DESC)

Then only SUM('Orders Rec'[GBP]) for Orders where 'Orders Rec'[Date] exists in the now filtered 'Date' table. 

I just can't quite fit it all together.

Please could anyone help - or suggest an easier solution for this? 

Thanks in advance

[amitchandak]

@Jenni-Sky , create a Rank column only on working dates. Now for any selected dates, you can get RANK -20  is last 20 working date

Working date = if([working Day]="True",[Date],blank())

WD Rank =Rankx(all(Date),[Working date ],,asc,dense)

The only problem is if you select a holiday on date slicer if will not work

rolling

Last 20 working dates Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[WD Rank]>=min('Date'[WD Rank])-20 && 'Date'[Week Rank]<=max('Date'[WD Rank])))

[Jenni-Sky · Accepted solution]

Thank you! 

A Rank Column has helped me reach my desired outcome. 

Your Last 20 Working Dates Sales measure did not quite give me the correct value. However by using your Ranked Column idea and tweaking the measure, the following measure gives my desired outcome: 

Last 20 Working Dates Sales = CALCULATE(

SUM('Orders Rec'[GBP]),

FILTER(

ALL('Date'),

'Date'[Date] < TODAY() && 'Date'[Date]>=LOOKUPVALUE('Date'[Date],'Date'[WD Rank],MAX('Date'[WD Rank])-20)

)

)