Forum Discussion
Solved
Calculate the last value per group
Hi team,
I need help to return the value of the last item (Doc), group by Contratc.
I created an index column. But I am not able to calculate this last value.
The solution can be in DAX or M language.
Base:
| Client | Contract | Doc | Date_Doc | Value_Doc | Index |
| Miguel Santos | 120630 | D745896 | 01/01/2020 | 150 | 1 |
| Miguel Santos | 120630 | D542185 | 01/01/2021 | 180 | 2 |
| Ana Matos | 115312 | D963521 | 15/05/2019 | 80 | 1 |
| Ana Matos | 115312 | D852951 | 15/05/2020 | 95 | 2 |
| Rute Cardoso | 117689 | D752145 | 10/02/2019 | 60 | 1 |
| Rute Cardoso | 117689 | D896358 | 10/08/2019 | 70 | 2 |
| Rute Cardoso | 117689 | D965745 | 10/02/2020 | 65 | 3 |
| Rute Cardoso | 117689 | D987321 | 10/08/2020 | 55 | 4 |
| Alice Maria | 174541 | D556395 | 15/06/2019 | 30 | 1 |
| Alice Maria | 174541 | D458741 | 15/06/2020 | 35 | 2 |
| Alice Maria | 174541 | D632856 | 15/06/2021 | 40 | 3 |
| Alice Maria | 256357 | D958968 | 20/04/2019 | 120 | 1 |
| Alice Maria | 256357 | D145789 | 20/04/2020 | 125 | 2 |
Final result:
| Client | Contract | Doc | Date_Doc | Last_Value_Doc |
| Miguel Santos | 120630 | D542185 | 01/01/2021 | 180 |
| Ana Matos | 115312 | D852951 | 15/05/2020 | 95 |
| Rute Cardoso | 117689 | D987321 | 10/08/2020 | 55 |
| Alice Maria | 174541 | D632856 | 15/06/2021 | 40 |
| Alice Maria | 256357 | D145789 | 20/04/2020 | 125 |
Grateful for the help.
You can use following formule
LastAvailableValue =var last_date = MAX('Table'[Date_Doc])RETURNCALCULATE(SUM('Table'[Value_Doc]),'Table'[Date_Doc] = last_date)Thanks,SayaliIf this post helps, then please consider Accept it as the solution to help others find it more quickly.
2 Replies
- sayaliredij
Solution Sage
You can use following formule
LastAvailableValue =var last_date = MAX('Table'[Date_Doc])RETURNCALCULATE(SUM('Table'[Value_Doc]),'Table'[Date_Doc] = last_date)Thanks,SayaliIf this post helps, then please consider Accept it as the solution to help others find it more quickly.Solved.
Thank`s.
