Forum Discussion
Helper IHelp with New Measure - Top N and Related Table
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)))
Super UserJenni-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)))