cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Michaela_Onu Frequent Visitor
Frequent Visitor

Calculating YoY % in DAX if some fields are missing values

Hi all, 

I suppose this will be pretty basic but I cannot get my way around it on my own. I have a simple file with revenue from various products from 2017 and 2018; now, some customers have purchased the product in one year, some in both, some in none.

I was using just a simple calculation of YoY %:=DIVIDE(CALCULATE([Billed revenue],'Date'[Year]="2018"),CALCULATE([Billed revenue],'Date'[Year]="2017",0))-1). This worked fine but when I have a look at the PowerPivot, it shows me so many -100% it looks awful, it is not really user-friendly and gets confusing as the number of customers is huge. I would prefer it to show blanks in case no purchase was made, but at the same time, I need to substract the 1 from the calculation as it makes no sense to show YoY growth of 103 % when i is actually 3 %. I tried to play with IFERROR but that did not solve my issue as there needs to be the -1, to see the YoY change only. 

Would anyone suggest a solution please? I have tried several different forums but have not really found what I was looking for. 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Jeltex Member
Member

Re: Calculating YoY % in DAX if some fields are missing values

Hi,

 

Please have a look at this.

 

The example used at the bottom of that page might solve your problem.

 

Kind regards,

2 REPLIES 2
Jeltex Member
Member

Re: Calculating YoY % in DAX if some fields are missing values

Hi,

 

Please have a look at this.

 

The example used at the bottom of that page might solve your problem.

 

Kind regards,

Michaela_Onu Frequent Visitor
Frequent Visitor

Re: Calculating YoY % in DAX if some fields are missing values

Amazing, this worked perfectly, cannot believe I did not know this function. Thanks!