Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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:
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
102 | |
68 | |
45 | |
37 | |
37 |