Get last 4 values/dates from a table

[Anonymous]

Hi Experts

I have a requirement to calculate 4 four values average.

My data is as such

Name	date	value
Ammonia	1/1/2023	1.25
Ammonia	1/7/2023	0.21
Ammonia	1/19/2023	0.3
Ammonia	1/22/2023	1.1
Ammonia	2/1/2023	3.11
Ammonia	2/4/2023	0.32
Ammonia	2/12/2023	0.91
Ammonia	2/23/2023	1.01
Ammonia	2/25/2023	2.43
Potassium	1/7/2023	0.02
Potassium	1/11/2023	0.68
Potassium	1/19/2023	0.81
Potassium	2/5/2023	1.66
Potassium	2/23/2023	1.25

I need to display 2 tables like this. One table with summary and bottom table with only last 4 values for each Name column

Name	Timeperiod	Average
Ammonia	2/4/2023 - 2/25/2023	1.2
Potassium	1/11/2023 - 2/23/2023	1.1

Name	date	value	avg
Ammonia	2/4/2023	0.32	1.2
Ammonia	2/12/2023	0.91	1.2
Ammonia	2/23/2023	1.01	1.2
Ammonia	2/25/2023	2.43	1.2
Potassium	1/11/2023	0.68	1.1
Potassium	1/19/2023	0.81	1.1
Potassium	2/5/2023	1.66	1.1
Potassium	2/23/2023	1.25	1.1

[danextian · Accepted solution]

Hi @Anonymous 

You can create a measure that virtually ranks the dates for each Name and filter that to ranks <=4

Latest 4 Value Avg = 
AVERAGEX (
    FILTER (
        SUMMARIZECOLUMNS (
            Data[Name],
            Data[date],
            "@rank", RANKX ( ALL ( Data[date] ), CALCULATE ( AVERAGE ( Data[date] ) ),, DESC, DENSE ),
            "@value", CALCULATE ( SUM ( Data[value] ) )
        ),
        [@rank] <= 4
    ),
    [@value]
)

And then another measure referencing the first one but returns a value only for those with ranks <= 4.

Name Avg = 
IF (
    NOT ( ISBLANK ( [Latest 4 Value Avg] ) ),
    CALCULATE ( [Latest 4 Value Avg], ALLEXCEPT ( Data, Data[Name] ) )
)

Please see the attached sample pbix.

[Anonymous]

@danextian, please also let me know to limit table only to display  rows for  last 4 dates. Because I want to display in my 2nd table with only last4 dates. With the above measure "Latest 4 Value Avg" not giving me blanks in my case so I'm not able to filter blank values from table

[danextian]

You can see in my example that Latest 4 Value Avg is showing blanks. Please share a sanitized copy of your pbix so I can inspect why.

[Anonymous]

Thanks for your proposed solution.  For the summarized table (table1) solution is working as expected.