Forum Discussion
Helper IHelp with New Measure - Top N and Related Table
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
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)))
2 Replies
- amitchandak
Super User
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-SkyThank 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)))
