cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PeterL1
Helper I
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.

CountrySALES 2018SALES 2017 SALES 2016
USA36,15227,37227,239
UK23,96222,48919,129
SWE27,40827,08522,061
NOR12,2849,2670
FIN9,66310,29010,290
POL29,73326,93325,270
BRA19,08214,5920
FRA12,58210,1978,811
POR13,78311,6848,469
ITA18,01014,23511,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
v-shex-msft
Community Support
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.

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=0Issue.PNG

 

 

Thanks

Peter

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:

5.PNG

 

Regards,

Xiaoxin Sheng

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

Hi @v-shex-msft

 

Is the above possible?

Thanks

Peter

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

 

Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

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.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!