cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mape
Frequent Visitor

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

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

 

 

11 REPLIES 11
Abduvali
Skilled Sharer
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

mape
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:

 

 201520162017
    
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:

 

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

@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 =DWorks like a charm =D

Regards

Abduvali

mape
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

 

Nombre20162017Total
Customer1370013700174002
Customer2370013700174002
Customer3370013700174002
Customer4370013700174002
Customer537001 37001
Customer6370013700174002
Customer737001 37001
Customer8370013700174002
Total296008222006518014

 

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:

NumVentaEjercicioClienteImporteVenta
12016C11500
22017C1500
32016C22000
42017C22000
52016C37500
62017C32500
72016C4600
82017C4600
92016C5450
102017C62100
112016C61100
122016C72650
132016C89001
142017C84500

 

thanks

best regards

 

Abduvali
Skilled Sharer
Skilled Sharer

@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]   <------

Capture44.PNG

 

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

 

Nombre20162017Total
Customer1370013700174002
Customer2370013700174002
Customer3370013700174002
Customer4370013700174002
Customer537001 37001
Customer6370013700174002
Customer737001 37001
Customer8370013700174002
Total296008222006518014
Abduvali
Skilled Sharer
Skilled Sharer

@mape,

 

 

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

Capture45.PNG

 

 

mape
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:

 

Captura.PNG

 

Abduvali
Skilled Sharer
Skilled Sharer

@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

 

Hope this helps and will explain how it works.

 

 

Regards

Abduvali

mape
Frequent Visitor

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
Frequent Visitor

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?

 

Smiley Happy

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.