If sales are blank previous years return this year, only on selected criterias

[PeterL1]

Hi Guys, 

I cannot work out how to calculate the following.

I have the following:

County Table.

Sales Table.

Date Table

Product Table

I've got a summary tab where my matrix will show the total sales and profit etc by country and by year.

Like the below.

Country	SALES 2018	SALES 2017	SALES 2016
USA	36,152	27,372	27,239
UK	23,962	22,489	19,129
SWE	27,408	27,085	22,061
NOR	12,284	9,267	0
FIN	9,663	10,290	10,290
POL	29,733	26,933	25,270
BRA	19,082	14,592	0
FRA	12,582	10,197	8,811
POR	13,783	11,684	8,469
ITA	18,010	14,235	11,723

So to the issue.

Where a country has not got any sales for 2016, i wish to replace it with last year i.e. 2017.

However i do not wish to do this for all the countries, because when i look at the product view it will show sales replication for all the countries. 

The below does not work for me (only for a Country overview but not down to product by country.

IF(

  CALCULATE (
    [Sales 2018],
    DATEADD ( 'Date'[Date], - 728 , DAY ))
= BLANK (),
    [Sales 2017],
    [Sales 2018])

I want to select Brazil and Norway, and tell the calculation that replace the 2016 figures for those 2 countries with 2017 figures.

Something like :

IF(

  CALCULATE (
    [Sales 2018], 'Countries'[Country] = "NOR" || "BRA",
    DATEADD ( 'Date'[Date], - 728 , DAY ))
= BLANK (),
    [Sales 2017],
    [Sales 2018])

[v-shex-msft]

Hi @PeterL1,

Can you please share some detail information to help us clarify your scenario? (such as sample data, pbix file, expected result,...)

Regards,
Xiaoxin Sheng

[PeterL1]

Hi @v-shex-msft

Below image, 

Where Brazil and Norway in the left most column (summary) has no sales for either 2017 or 2016, i want to use the full sales years data instead.

E.g. Brazil's total sales for 2016 are 0, so want to use the 2017 sales, but only for Brazil.

Norway has no sales for 2017, so i want to use the 2018 sales, but only for Norway, etc.

If i would calculate,

= IF (Sales 2016 = BLANK(), 2017 Sales, Sales 2016)

It will then duplicate the sales for the other Countries that has blank sales for 2016 in the right most table where my products are, which is incorrect.

Also see link to the PBIX file with the information as well.

https://www.dropbox.com/s/13uc1wrwhqlp9bz/Blank%20Sales%20PBIX.pbix?dl=0

Thanks

Peter

[v-shex-msft]

Hi @PeterL1,

You can refer to below formula to get replaced sell value.

Sales 2016 = 
VAR temp =
    CALCULATE (
        SUM ( Sales[SELL NET] ),
        'Date'[Year] = 2016,
        VALUES ( Products[DESCRIPTION] )
    )
RETURN
    IF ( temp <> 0 && [Sales 2018] <> BLANK (), temp, [Sales 2017] )


Sales 2017 = 
VAR temp =
    CALCULATE (
        SUM ( Sales[SELL NET] ),
        'Date'[Year] = 2017,
        VALUES ( Products[DESCRIPTION] )
    )
RETURN
    IF ( temp <> 0 && [Sales 2018] <> BLANK (), temp, [Sales 2018] )

Sales 2018 = 
CALCULATE (
    SUM ( Sales[SELL NET] ),
    'Date'[Year] = 2018,
    VALUES ( Products[DESCRIPTION] )
)

Result:

Regards,

Xiaoxin Sheng

[PeterL1]

Hi @v-shex-msft

Is the above possible?

Thanks

Peter

[PeterL1]

Hi @v-shex-msft,

Not quite what i need,

What you have written will bring out, all blank sales, on product description level, where the sale is blank to return the previous year values.

What i need:

If the Total sales for a country for a year is blank, replace the zero values for that country only with the previous year values.

For Example.

Where USA has 0 sales on a product called Pine Cone for 2016, but has sales for other products in 2016, i want the Pine Cone to say 0.

Where Brazil has no sales at all for 2016, i want to replicate the sales for 2017 to 2016.

So only replicate if the full year is blank.

I hope this makes sense.

Thanks

Peter