cancel
Showing results for
Did you mean:
Helper I

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

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

5 REPLIES 5
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Helper I

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.

Thanks

Peter

Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Helper I

Is the above possible?

Thanks

Peter

Helper I

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

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.