In a matrix, how I can replace values in empty cells with values of previous cells ?

[mape]

Hi everyone!! I need help about this...

Measure of sales per year and in case there is no information for that year, show the sales of the previous year.

That is, given a series of sales per year for a series of customers, it would be necessary if, for a given year, that customer does not have a value, to fill the cell with the value of the last year that had value.

Example

Customer A (SALES 2017 = 1000, SALES 2016 = 1200, SALES 2015 = 900)

Client B (SALES 2016 = 500, SALES 2015 = 700)

In a matrix of sales of customer x year the crossing of Client B and 2017 would be empty and what I want is for it to take the value of the previous year that contains data, specifically 2016), that is to say that the table will remain as follows:

                 2015 2016 2017

Customer A 900 1200 1000

Client B 700 500 500

That value of 500 in 2017 I would have to put my measurement.

Thank you very much!!!!

[Abduvali]

Hi @mape,

You can create a Column with IF statement in it like:

• COLUMN = if(YourTable[Sales] = BLANK(), 0, SUM(YourTable[SALES])
• and then use that column for to display sales this should solve having blank cells in a matrix

Regards

Abduvali

[mape]

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...

🙂

[Abduvali]

@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

[mape]

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

[Abduvali]

@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

[mape]

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

[Abduvali]

@mape,

In your matrix table drop your date field [Fecha] and from hierarchy only keep year:

[mape]

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:

[Abduvali]

@mape,

You have to understand the solution, the following DAX is built on MM/DD/YYYY date format field so to make it easier follow the link to my Power BI library and download the solution =D

• https://www.pbiusergroup.com/viewdocument/filling-empty-cells-with-last-year

Hope this helps and will explain how it works.

Regards

Abduvali

[mape]

Hi @Abduvali

That is fantastic!! thank you, now it works, I was creating a calculated column instead of a measurement.

I have a new doubt, if I wanted to do it for the months, would it work the same way? , for example, if call ustomers all have sales in May but in November only some customer have it, and I want those that are blank in November to show me the May sales, how would it be in this case?

🙂

[mape]

Hi @Abduvali

That is fantastic!! thank you, now it works, I was creating a calculated column instead of a measurement.

I have a new doubt, if I wanted to do it for the months, would it work the same way? , for example, if call ustomers all have sales in May but in November only some customer have it, and I want those that are blank in November to show me the May sales, how would it be in this case?