Forum Discussion
In a matrix, how I can replace values in empty cells with values of previous cells ?
Hi Abduvali,
I have tested it with steps you say in your response but I don't get the same results.
Mi new calculate column is:
lastyear =
var l = SUM(Ventas[ImporteVenta])
var c = CALCULATE(SUM(Ventas[ImporteVenta]);FILTER(ALLSELECTED(Ventas[Ejercicio]);Ventas[Ejercicio]-1))
return
if(l=BLANK();c;l)
Look the result
| Nombre | 2016 | 2017 | Total |
| Customer1 | 37001 | 37001 | 74002 |
| Customer2 | 37001 | 37001 | 74002 |
| Customer3 | 37001 | 37001 | 74002 |
| Customer4 | 37001 | 37001 | 74002 |
| Customer5 | 37001 | 37001 | |
| Customer6 | 37001 | 37001 | 74002 |
| Customer7 | 37001 | 37001 | |
| Customer8 | 37001 | 37001 | 74002 |
| Total | 296008 | 222006 | 518014 |
All cells shows the sum and for the cell of Customer5 and Customer7 with year 2017 these cells are empty.
it is possible that since there is no value in the table, it will not show anything in those cells
The original Sales table is:
| NumVenta | Ejercicio | Cliente | ImporteVenta |
| 1 | 2016 | C1 | 1500 |
| 2 | 2017 | C1 | 500 |
| 3 | 2016 | C2 | 2000 |
| 4 | 2017 | C2 | 2000 |
| 5 | 2016 | C3 | 7500 |
| 6 | 2017 | C3 | 2500 |
| 7 | 2016 | C4 | 600 |
| 8 | 2017 | C4 | 600 |
| 9 | 2016 | C5 | 450 |
| 10 | 2017 | C6 | 2100 |
| 11 | 2016 | C6 | 1100 |
| 12 | 2016 | C7 | 2650 |
| 13 | 2016 | C8 | 9001 |
| 14 | 2017 | C8 | 4500 |
thanks
best regards
mape,
See my formula I have used date field there (my date field format is DD/MM/YYYY) and extended it to YEAR using DAX
- Sheet6[End].[Year] <------
So do the same if you will use year only then it will calculate all values for the year. If you dont have a date field then concatenate your day/month/year columns and then use it.
Regards
Abduvali
Hi Abduvali, I see but the proble is the same. If I replaced that field by a full date (named "Fecha") , finally the matrix shows the same result that I got before:
lastyear =
var l = SUM(Ventas[ImporteVenta])
var c = CALCULATE(SUM(Ventas[ImporteVenta]);FILTER(ALLSELECTED(Ventas[Fecha].[year]);Ventas[Fecha].[Year]-1))
return
if(l=BLANK();c;l)the matrix result like:
Nombre 2016 2017 Total Customer1 37001 37001 74002 Customer2 37001 37001 74002 Customer3 37001 37001 74002 Customer4 37001 37001 74002 Customer5 37001 37001 Customer6 37001 37001 74002 Customer7 37001 37001 Customer8 37001 37001 74002 Total 296008 222006 518014 Hi Abduvali , sorry for my english, I think that we don't speak about the same.
Assuming there is only one sale per year per customer and in some cases if there is not one I want to take the previous year, I do not care about the date, month, day ... only the year. It keeps showing me what we are talking about, removing the rest of the elements from the date hierarchy
Look the image: