Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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!

  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi,

     

    Please have a look at this.

     

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

     

    Kind regards,

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi,

     

    Please have a look at this.

     

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

     

    Kind regards,

    • Anonymous's avatar
      Anonymous
      Not applicable

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