YoY Change (in percentage terms) Showing -100% for Years with No Data!

[Terp]

I apologize, but will preface my question with another question, given that I am a total rookie and trying to learn some best practices. 🙂

• I have no qualms adding a column to my Excel file, which drives the visuals, to caluclate percentage changes; is this approach more efficient than having PowerBi call the numbers and natively do the math? Or in the bigger picture, is using 'measures' the smarter workflow?

Now my original question...used a quick measure to show the year-over-year change in a horizontal matrix. It works fine, except when we get to the 2021 data...only have Jan, Feb, and Mar data so far, so for the rest of the year in the matrix when 2021 is selected, it's computing -100% and populating the row all the way across and can't figure out how to get it to stop (checked the parameters in the 'model' area, but didn't see any settings neither there nor in the matrix visual settings to disable, say, show nulls or no values or whatever you call it. 🙂

Is there a way, without using fancy DAX (on my list next), to simply disable this "-100%"?

(I can doing the calculations myself in the spreadsheet, so maybe I answered both of my questions????) 🙂

[v-yangliu-msft · Accepted solution]

Hi  @Terp ,

I created some data:

From January 1, 2020 to March 5, 2021

Here are the steps you can follow：

1. Create measure.

precent =
var _1=SUM('Table'[amount])
var _2=SUMX(FILTER(ALL('Table'),MONTH('Table'[date])=MONTH(MAX('Table'[date]))&&YEAR('Table'[date])=YEAR(MAX('Table'[date]))-1),'Table'[amount])
return
IF(_1=BLANK(),BLANK(),DIVIDE(_1-_2,_2))

2. Result:

If the answer is not what you expected, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data

Best Regards,

Liu Yang

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