Forum Discussion
Solved
Calculate Table Formula
Hi All,
Can you please help me with the below requirement.
I have existing table(Table_1) with below details but need to create calculated table which has max date from each id.
(Table_1)
| Id | Amount | Date |
| 1 | 201007 | 31-Dec-19 |
| 1 | 175947 | 30-Sep-19 |
| 1 | 198300 | 30-Jun-19 |
| 2 | 7872 | 31-Dec-19 |
| 2 | 7551 | 30-Sep-19 |
| 2 | 6946 | 30-Jun-19 |
| 3 | 24087 | 31-Mar-20 |
| 3 | 24087 | 31-Dec-19 |
| 3 | 22513 | 30-Sep-19 |
| 3 | 32767 | 30-Jun-19 |
| 4 | 32260 | 31-Dec-19 |
| 4 | 44933 | 30-Sep-19 |
| 4 | 37113 | 30-Jun-19 |
| 5 | 12707 | 31-Dec-19 |
| 5 | 21533 | 30-Sep-19 |
| 5 | 15347 | 30-Jun-19 |
Required Table
| Id | Amount | Date |
| 1 | 201007 | 31-Dec-19 |
| 2 | 7872 | 31-Dec-19 |
| 3 | 24087 | 31-Mar-20 |
| 4 | 32260 | 31-Dec-19 |
| 5 | 12707 | 31-Dec-19 |
@calculated table
pls try this
Table 2 = ADDCOLUMNS( SUMMARIZE('Table','Table'[Id],"date",max('Table'[Date])),"amount",maxx(FILTER('Table','Table'[Id]=EARLIER('Table'[Id])&&'Table'[Date]=EARLIER([Date])),'Table'[Amount]))
1 Reply
pls try this
Table 2 = ADDCOLUMNS( SUMMARIZE('Table','Table'[Id],"date",max('Table'[Date])),"amount",maxx(FILTER('Table','Table'[Id]=EARLIER('Table'[Id])&&'Table'[Date]=EARLIER([Date])),'Table'[Amount]))