Compound annual growth not calculating for data with sales by day

DAX DAX Calculations
1

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.

File is here https://www.dropbox.com/s/50miu8jli5xd6yt/CAGR.pbix?dl=0

1

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.

File is here: https://www.dropbox.com/s/v3w9jdak56zngyy/Mock%20Data%20PBI.pbix?dl=0

2
2817×222 27.3 KB

What am I doing wrong?

Many thanks,

Tim

2

@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

image
image699×215 7.61 KB

2 Likes
3

That works!

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

Thank you AntrikshSharma.

4

@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
5

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
Capture

6

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

Capture
Capture867×441 48.1 KB

Any ideas would be much appreciated @AntrikshSharma

7

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
9

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.

Kindly create a new thread for your inquiry.

1 Like
10

that’s fine, I got the answer I was asking for but it turns out I was asking the wrong question in the first place. The CAGR I calculated wasn’t a CAGR at all, the formula is different but I worked it out either way.