# Compound annual growth not calculating for data with sales by day

I’ve built my CAGR (compound annual growth) dax formula based on Curbal’s video https://www.youtube.com/watch?v=jIb--ImdjNg

I have the calculation working on a small dataset where all amounts are nicely summarised into years.

Then I’ve tried to apply the same formula to a larger dataset where sales data is by day.

CAGR is showing as “1” which is incorrect as it should be the “sales var to LY %” of this year + all other years preceding.

What am I doing wrong?

Many thanks,

Tim

@Timmay

Context Transition on a Fact Table.

``````Compound Annual Growth % =
VAR MaxVisibleYear =
MAX ( 'Date Table'[Year] )
VAR Result =
CALCULATE (
PRODUCTX ( VALUES ( 'Date Table'[Year] ), 1 + [Sale Var to LY %] ),
'Date Table'[Year] <= MaxVisibleYear,
REMOVEFILTERS ( 'Date Table' )
)
RETURN
Result * 100
``````

2 Likes

That works!

Fantastic, looks like I have some learning to do about context transition.

Thank you AntrikshSharma.

@Timmay Yup, the idea is simple, don’t invoke Context Transition on a table that contains duplicates ( Either you know that it contains duplicates or there is a possibility it might contain duplicates ) and by design Fact table will generally contain duplicates unless you have a primary key such as an Order ID which is a bad thing in Tabular models, so always initiate Context Transition on a dimension table and let the relationships do the rest of the work.

2 Likes

I finally got a chance to try this on my live dataset & unfortunately I’m getting an unexpected result. Sorry I can’t share the pbi file (company data). Any idea why it could be happening? @AntrikshSharma

Turns out I was using year in the formula but fyear in the table, context.

Looking better but not there yet, calculations still coming up incorrect for every year after the first 2 years

Any ideas would be much appreciated @AntrikshSharma

HI @Timmay. You’re posting to a solved thread, so it’s likely that your post will not reach forum members who most often only search for un solved threads; please create a new thread for a new question with a sample PBIX and marked-up screenshot of the visual that’s the issue, noting which rows specifically are incorrect. Also, by calling-out a particular member, you’re encouraging other forum members to not review your post; please refrain from asking for help from a specific member.
Greg

3 Likes

Hi @Timmay, as mentioned by @Greg above we discourage posting on a previously solved thread and singling out a forum user to answer your question.

You may check this how-to guide for reference - How To Use The Enterprise DNA Support Forum

We’ll be marking this thread as solved again. If you need further assistance on using the forum, feel free to message anytime.