Forum Discussion

sr97's avatar
sr97
Frequent Visitor
1 year ago
Solved

Help with calculating Compound monthly growth rate

Hi all, 

 

Does anyone have inputs on how to calculate a compund monthly growth rate (CMGR) (see below)? 

 

Each supplier has a volume for a given month. For some months the volume is blank, which will influence how you calculate the CMGR. In some cases you cannot calculate the CMGR, as for Supplier Z. 

 

 

Need Help
  • v-ssriganesh's avatar
    v-ssriganesh
    1 year ago

    Hi sr97,
    Thank you for reaching out to the Microsoft Fabric Forum Community.

     I’ve reproduced your scenario using sample data matching your raw data structure and achieved the expected output. Create the fallowing dax measure to calculate CMGR.

    CMGR = 
VAR MinMonth = 
    CALCULATE(MIN('cmgr'[MonthNumber]), FILTER('cmgr', 'cmgr'[Value] > 0 && 'cmgr'[Supplier] = SELECTEDVALUE('cmgr'[Supplier])))
    VAR MaxMonth = 
    CALCULATE(MAX('cmgr'[MonthNumber]), FILTER('cmgr', 'cmgr'[Value] > 0 && 'cmgr'[Supplier] = SELECTEDVALUE('cmgr'[Supplier])))
    VAR V_i = 
    CALCULATE(
        FIRSTNONBLANK('cmgr'[Value], 1),
        'cmgr'[MonthNumber] = MinMonth,
        'cmgr'[Supplier] = SELECTEDVALUE('cmgr'[Supplier])
    )
    VAR V_f = 
    CALCULATE(
        LASTNONBLANK('cmgr'[Value], 1),
        'cmgr'[MonthNumber] = MaxMonth,
        'cmgr'[Supplier] = SELECTEDVALUE('cmgr'[Supplier])
    )
    VAR MonthCount = 
    MaxMonth - MinMonth



RETURN 
    IF(MonthCount > 0, 
        ( (V_f / V_i) ^ (1 / MonthCount) ) - 1, 
        BLANK()
    )

    The .pbix file attached demonstrates this working solution with your sample data.

    If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
    Thank you.

1 Reply

  • v-ssriganesh's avatar
    v-ssriganesh
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi sr97,
    Thank you for reaching out to the Microsoft Fabric Forum Community.

     I’ve reproduced your scenario using sample data matching your raw data structure and achieved the expected output. Create the fallowing dax measure to calculate CMGR.

    CMGR = 
VAR MinMonth = 
    CALCULATE(MIN('cmgr'[MonthNumber]), FILTER('cmgr', 'cmgr'[Value] > 0 && 'cmgr'[Supplier] = SELECTEDVALUE('cmgr'[Supplier])))
    VAR MaxMonth = 
    CALCULATE(MAX('cmgr'[MonthNumber]), FILTER('cmgr', 'cmgr'[Value] > 0 && 'cmgr'[Supplier] = SELECTEDVALUE('cmgr'[Supplier])))
    VAR V_i = 
    CALCULATE(
        FIRSTNONBLANK('cmgr'[Value], 1),
        'cmgr'[MonthNumber] = MinMonth,
        'cmgr'[Supplier] = SELECTEDVALUE('cmgr'[Supplier])
    )
    VAR V_f = 
    CALCULATE(
        LASTNONBLANK('cmgr'[Value], 1),
        'cmgr'[MonthNumber] = MaxMonth,
        'cmgr'[Supplier] = SELECTEDVALUE('cmgr'[Supplier])
    )
    VAR MonthCount = 
    MaxMonth - MinMonth



RETURN 
    IF(MonthCount > 0, 
        ( (V_f / V_i) ^ (1 / MonthCount) ) - 1, 
        BLANK()
    )

    The .pbix file attached demonstrates this working solution with your sample data.

    If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
    Thank you.