I need a measure to add the last non blank value from column SaldoConta
from the table pcsa
below from each column conta
.
The criteria to define last non blank is the one with maximum value in column AnoMes
.
conta ano mes AnoMes SaldoConta
110 2017 2 201702 112,56
110 2017 0 201700 112,56
111 2017 4 201704 70,47
111 2017 0 201700 78,06
111 2017 1 201701 70,47
111 2017 2 201702 70,47
112 2017 8 201708 100,00
112 2017 3 201703 242,16
112 2017 10 201710 100,00
112 2017 2 201702 200,00
112 2017 11 201711 100,00
112 2017 12 201712 100,00
112 2017 5 201705 240,75
112 2017 1 201701 200,00
112 2017 4 201704 242,16
112 2017 0 201700 100,00
112 2017 9 201709 200,00
112 2017 6 201706 683,67
112 2017 7 201707 200,00
113 2017 2 201702 72 865,90
113 2017 0 201700 21 114,37
113 2017 3 201703 55 572,24
113 2017 1 201701 39 967,48
114 2017 11 201711 0,00
114 2017 0 201700 92,59
114 2017 8 201708 46,40
114 2017 7 201707 84,50
115 2017 0 201700 12,00
116 2017 0 201700 8,27
I've tried a million different DAX expressions but it seems impossible to get what I want.
It should add only the LAST nonblank value in AnoMes, so for conta
110, it should be 112,56
.
So, the table above should be:
conta ano mes AnoMes SaldoConta
110 2017 2 201702 112,56
111 2017 4 201704 70,47
112 2017 12 201712 100,00
113 2017 3 201703 55 572,24
114 2017 11 201711 0,00
115 2017 0 201700 12,00
116 2017 0 201700 8,27