cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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

11 REPLIES 11
Skilled Sharer

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

Frequent Visitor

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

🙂

Skilled Sharer

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

Frequent Visitor

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

Skilled Sharer

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

Frequent Visitor

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
Skilled Sharer

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

Frequent Visitor

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:

Skilled Sharer

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

Hope this helps and will explain how it works.

Regards

Abduvali

Frequent Visitor

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?

🙂

Frequent Visitor

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?