Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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:
@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])))
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:
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |