Forum Discussion
Get last 4 values/dates from a table
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 |
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.
4 Replies
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.
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
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.