Hi everyone! I am working on a DAX calculation following the video “How To Track Sales Trends Through Time in Power BI using DAX” in the Business Analytics Series. However, something I noticed is when a customer does not have any sales in Q2, and then has sales in Q3, the %Sales Growth is -100%? Is there a workaround/reasoning for this?
Welcome to the forum – great to have you here!
Here’s the measure code being used for quarterly growth:
Quarterly Growth =
DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], DATEADD ( Dates[Date], -1, QUARTER ) ), 0 ) - 1
When there are no prior quarter sales, the DIVIDE function is evaluating to 0 (the third parameter in the DIVIDE statement, used when the second parameter evaluates out to zero to prevent a divide by zero error).
One solution is to wrap to the DIVIDE statement in an SWITCH(TRUE() )statement to catch cases where prior Q sales are 0 or blank. I would rewrite it as follows, using variables for clarity:
Quarterly Growth = VAR TotSalesLY = CALCULATE ( [Total Sales], DATEADD ( Dates[Date], -1, QUARTER ) ) VAR QGrowth = DIVIDE ( [Total Sales], TotSalesLY, 0 ) - 1 RETURN SWITCH ( TRUE (), TotSalesLY = 0, BLANK (), TotSalesLY = BLANK(), BLANK (), QGrowth )
I hope this is helpful.
P.S. I wasn’t sure what you preferred to return instead of the -100%, so I just returned a blank. If you have an alternate value you rather use to denote that case, just replace the highlighted blanks below with your preferred value.
Thank you so much for this suggestion! It helped clear up the -100% I was seeing. However I still see that the Growth for Q2 when Q1 is empty is blank. Do you have any suggestions for this?
So the measure is doing exactly what we’ve asked it to do. The question now is did we ask it to do the right thing in terms of denoting that particular situation? We can change what the measure returns in that scenario just by changing the return value highlighted by the green arrow below. However, I’m not sure what the finance best practice is in terms of denoting what mathematically is basically an infinite percent change.
Perhaps some of the finance/accounting experts on the forum can weigh in on what the proper way to denote this QonQ% growth is? (paging @GarryA…)
Hi @msheladia , we’ve noticed that no response has been received from you since the 22nd of May. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
Particularly, the Comparison time patterns are what you should review.
Post a sample data model to the site for more assistance please so we can check your calendar and dimension setup.
Thanks for the response. That’s a great reference resource. However, our question here is really a finance issue, not a DAX issue. The DAX is working fine now, but we’re not sure what the proper value is for it to return for QoQ% growth when the sales figure for the earlier Q is blank or zero. What’s considered standard practice in that case?
When the starting period value is zero or negative in scenarios, you typically see a “dash” or “N/A” as the result.
Analyst’s normally don’t want to see 100% but probably an indicator “N/A” or “dash” that means the change in that period is meaningless. Showing a 100% when values go from 0 to another data point is something you would need to decide as the management accountant.
You will see similar scenarios when looking at public company’s accounts of google finance with say a loss last quarter, the PE ratio will be blank. So if a company doesn’t have earnings the PE ratio is meaningless. Likewise, if a sales ledger has a period where the sales are say negative then the percent change could be viewed as meaningless.
Its an interesting subject matter and interpretation is based on your management philosophy. I’m sure others would have come across this design issue in the past.
Thanks for the detailed response - exactly what we needed.
Hi @msheladia, Welcome to the Forum! We hope that you are having a great experience so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!