Calculate Previous year sales if current year is 0

[Atifz]

Hi,

My data is in a simple model with a sales table like below, and for each client I want to calculate 2021 sales only if they have 0 sales for 2022 and vice- versa.

I also need to be able to aggregate this up to Website level

Website	Client	Product	Date	Amount
WebA	ClientA	ProductA	01/01/2021	85
WebB	ClientA	ProductB	01/01/2021	17
WebB	ClientB	ProductC	01/01/2021	82
WebA	ClientC	ProductD	01/01/2021	65
WebA	ClientA	ProductE	01/01/2022	69

[v-yadongf-msft · Accepted solution]

Hi @Atifz ,

This is my test table:

Please ccreate two measures:

Sum_of_client = 
var sum2021 = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Client] = SELECTEDVALUE('Table'[Client]) && YEAR('Table'[Date]) = 2021))
var sum_amount = CALCULATE(MIN('Table'[Amount]),FILTER('Table','Table'[Client] = SELECTEDVALUE('Table'[Client]) && YEAR('Table'[Date]) = 2022))
return
IF(sum_amount = 0,sum2021)

Sum_of_website = 
var sum2021 = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Website] = SELECTEDVALUE('Table'[Website]) && YEAR('Table'[Date]) = 2021))
var sum_amount = CALCULATE(MIN('Table'[Amount]),FILTER('Table','Table'[Website] = SELECTEDVALUE('Table'[Website]) && YEAR('Table'[Date]) = 2022))
return
IF(sum_amount = 0,sum2021)

Then you can calculate Previous year sales if current year is 0.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Atifz Well, maybe something like:

Measure =
  VAR __Client = MAX('Table'[Client])
  VAR __Table2021 = FILTER('Table',YEAR([Date])=2021 && [Client] = __Client)
  VAR __Table2022 = FILTER('Table',YEAR([Date])=2022 && [Client] = __Client)
RETURN
  IF(ISBLANK(__Table2022),SUMX(__Table2021,[Amount]), SUMX(__Table2022,[Amount]))

[Atifz]

@Greg_Deckler 

Thanks for this but I get the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value". 

[Greg_Deckler]

@Atifz My bad:

Measure = 
  VAR __Client = MAX('Table'[Client])
  VAR __Table2021 = FILTER('Table',YEAR([Date])=2021 && [Client] = __Client)
  VAR __Table2022 = FILTER('Table',YEAR([Date])=2022 && [Client] = __Client)
RETURN
  IF(COUNTROWS(__Table2022) + 0 = 0,SUMX(__Table2021,[Amount]), SUMX(__Table2022,[Amount]))

[Atifz]

@Greg_Deckler Thanks for this, it's working now at client level but the totals are not correct. If I want to summarise at a website level, the numbers don't match

[Greg_Deckler]

@Atifz See this: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community

Also: This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907