Show sales of older products when I try to compare it with newer one

[LarBo]

Hi team,

I have this challenge, I’ve new products that I need to understand their performance this year, but as a reference, as I don’t have information of last year, business wants to compare it with similar older products, for example see table 1:

So I have to prepare table 2 with this information:

Where Sales Last Year for New Products in fact doesn’t exist, so I must fill with the old similar product sales.

I have defined de first table, but I’m not figuring out how to do the DAX to generate the second table, do you know how can I do this please?

Thank you!

[Nathaniel_C]

Hi @LarBo ,

Where does the sales last year info reside?

Please read this post to get your question answered more quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Nathaniel

[LarBo]

Hi Nathaniel, sorry you are right, I've created a set of data as example, hope this is more clear, if not I will gladly try again.

I have sales amount recorded for two years from several sellers (name column), information attached in Datos 1.xlsx  first tab, and I need to evaluate performance in Year 2 against Year 1, but in the cases I don’t have selling information for Year 1, Luis and Julia, I will need to use another sellers Year 1 information as from Juan and Rita respectively, this relation is given in table Datos 1.xlsx second tab, too.

I need to figure it out how to do the DAX to fill 51.073 in Year 1 for Luis and 40.286 for Rita, so I will be able to measure the performance for each one, I attach pbix file, do you know how can I do this please?

Thank you!

[Nathaniel_C · Accepted solution]

Hi @LarBo ,
See VentasBase1,Not elegant - hard coded the number in, tried V for Juan, which gives us 51k, but does not substitute in for the hard code.May have to work with Ventas G1 . Because of the relationship, should be able to follow that to get the replacement value. Hopefully this will get you down the right route.

Nathaniel MY PBIX PBIX FILE 

[LarBo]

Hi @Nathaniel_C, thank you a lot, you give me the solution I was looking for, I just added one sentence to change the hard coded number you write for 51K, modified meassure from:

V for Juan = CALCULATE('h Ventas'[VENTAS];'h Ventas'[Gestion]="1";'h Ventas'[Nombre]="JUAN")

to

V for Juan = CALCULATE('h Ventas'[VENTAS];all('h Ventas');'h Ventas'[Gestion]="1";'h Ventas'[Nombre]="JUAN")

And now the measure you wrote works fine!

ventasBase1 =

VAR _Luis = "LUIS"

var _replaceJ = [V for Juan]

var _replaceR = [V for Rita]

var _calc =IF(MAX('h Ventas'[Nombre])=_Luis;_replaceJ; IF(MAX('h Ventas'[Nombre])="JULIA";_replaceR;[VENTAS BASE]))

return _calc

I add %Avance for calculate performace as in this image:

The final pbix document is attached if is usefull for other person, thank you again