Forum Discussion
In a matrix, how I can replace values in empty cells with values of previous cells ?
Hi Abduvali, thanks for you quick response, in fact the result isn't as I expected.
By default with the field "Sales" I have this matrix:
| 2015 | 2016 | 2017 | |
| Customer A | 900,00 € | 1.200,00 € | 1.000,00 € |
| Customer B | 700,00 € | 500,00 € |
The new measure or calculated column that I want , it shows like this:
| 2015 | 2016 | 2017 | |
| Customer A | 900,00 € | 1.200,00 € | 1.000,00 € |
| Customer B | 700,00 € | 500,00 € | 500,00 € |
i.e., if cell is blank it could show the "last sales" in the previous just period
If you can give me another idea, thanks...
:)
mape,
That was tricky =D
This should work for you just create a new column:
- lastyear =
var l = SUM(Sheet6[Sales])
var c = CALCULATE(SUM(Sheet6[Sales]),FILTER(ALLSELECTED(Sheet6[End].[Year]),Sheet6[End].[Year]-1))return
if(l=BLANK(),c,l)
Works like a charm =D
Regards
Abduvali
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