Forum Discussion

mape's avatar
mape
Frequent Visitor
8 years ago

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 ...
Abduvali's avatar
Abduvali
Skilled Sharer
8 years ago

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's avatar
mape
Frequent Visitor
8 years ago

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's avatar
    Abduvali
    Skilled Sharer
    8 years ago

    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's avatar
      mape
      Frequent Visitor
      8 years ago

      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's avatar
        Abduvali
        Skilled Sharer
        8 years ago

        mape,

         

         

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