cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Help 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?

1 ACCEPTED SOLUTION
Helper I

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 2
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])))

Helper I

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)
)
)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.