Forum Discussion

JST15's avatar
JST15
Helper I
1 year ago
Solved

Annualizing Data to Include in Matrix Table with History

Hi very smart folks! I have an underlying set of data that includes five years of historic data for volume. My boss wants a simply matrix table that shows volume by year plus a few other metrics. Th...
Need Help
Tips and Tricks
  • danextian's avatar
    danextian
    1 year ago

    hI JST15 

    To evaluate calculation by year and sum them up, use SUMX on a virtual table.

    My Measure =
VAR MaxYear =
    CALCULATE ( MAX ( Dates[Year] ), ALLSELECTED ( Dates ) ) // Finds the max year in the Dates table based on current selection, use ALL to ignore slicer selections/filters on Dates
VAR MaxMonthNumber =
    MONTH ( CALCULATE ( MAX ( Dates[Date] ), ALLSELECTED ( Dates ) ) ) // Finds the month number of the max date in the Dates table based on current selection
RETURN
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE ( Fact, Fact[Year] ),
            "@qty",
                IF (
                    Fact[Year] = MaxYear,
                    DIVIDE ( [Qty Measure], MaxMonthNumber ) * 12,
                    [Qty Measure]
                )
        ),
        [@qty]
    )
Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

Hi JST15 , please try the following DAX:

 

1- Annualizing the 2024

AnnualizedVolume2024 = 
VAR YTDVolume = CALCULATE(SUM(YourTable[Volume]), YourTable[Year] = 2024)
RETURN (YTDVolume / 10) * 12

 

2. Create a CAGR measure

CAGR = 
VAR StartValue = CALCULATE(SUM(YourTable[Volume]), YourTable[Year] = 2019)
VAR EndValue = [AnnualizedVolume2024]
VAR Years = 2024 - 2019
RETURN ( (EndValue / StartValue) ^ (1 / Years) ) - 1

 

I hope this help you, if yes, please give kudo and mark as solution.

 

Thank you