topic Re: SUM last 5 records in DAX Commands and Tips

SUM last 5 records

Anonymous — Wed, 16 Sep 2020 15:46:55 GMT

Data Structure: I have grouped each individual and assigned a sub-group index value in power query to each result for that individual (see example below)

Table Name : 'Results'

Name	Force (N)	Sub-Group Index
Bill	130	1
Bill	140	2
Bill	150	3
Bill	130	4
Bill	125	5
Bill	140	6
Ted	180	1
Ted	155	2
Ted	140	3
Ted	130	4
Ted	160	5
Ted	170	6
Ted	170	7

Aim: I wish to SUM the values of the results table based on the last 5 records for eac individual. My logic of including a sub-group Index was to to try and calculate the sum based off the TOPN ranked DESC - not sure if this is a good approach?

Any help would be greatly appreciated!

Re: SUM last 5 records

lbendlin — Wed, 16 Sep 2020 18:44:25 GMT

yes, that's a good approach. Here is one possible implementation

Last5 = var n = SELECTEDVALUE(Results[Name]) var t = TOPN(5,filter(all(Results),Results[Name]=n),Results[Sub-Group Index],DESC) return sumx(t,[Force (N) ])

It doesn't give you nice totals. Not sure if that is important.

Re: SUM last 5 records

lbendlin — Wed, 16 Sep 2020 19:58:22 GMT

Here is a version that also handles the totals correctly.

Last5v2 = var n = SELECTCOLUMNS(VALUES(Results[Name]),"Name",Results[Name]) var s = ADDCOLUMNS(n,"v",sumx(TOPN(5,filter(Results,Results[Name]=[Name]),Results[Sub-Group Index],DESC),[Force (N) ])) return sumx(s,[v])

Re: SUM last 5 records

Anonymous — Thu, 17 Sep 2020 08:30:42 GMT

lbendlin You sir are a genius! It works perfectly and the way you've laid it out it makes so much sense. Thank you so much!