Annualizing Data to Include in Matrix Table with History

[JST15]

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. The volume data includes transactions through today. Here's my issue. He wants the 2024 volume to be annualized using the actuals through October (YTD-October/10*12). The screenshot below shows the view and the 2024 amount (707) which is volume through today. Creating a measure to use in a card is easy. How can I manipulate the data table or below matrix to annualize the 2024 amount? Plus have it seamlessly incorporated into the matrix table. Essentially, it looks just like the below table but the 707 is annualized so the final amount is 848. Or any suggestions folks have. I greatly appreciate the help!

Side question: If you can calculate off a table, would I be able to add a CAGR to the below matrix and show it under the 2024 row? This community is the best, thank you!!

[danextian · Accepted solution]

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]
    )

[JST15]

Hi @danextian . Thank you for the reply! I think my inexperience with Power BI is getting in my own way, as I can't get your solution to work. Though I think it's the one I'm looking for. When recreating your code for my work I get an error "The expression specified in the query is not a valid table expression". Any ideas what i'm doing wroing? I admit, I've never worked in a virtual table before so I've been reading up on them. Seem super helpful. Once I get it working, you're saying I can get an output that looks like the cutout I provided but shows the 2024 value as annualized? 

Annualized = 
 VAR MaxYear = CALCULATE(MAX('Calendar'[Year]),ALLSELECTED('Calendar'))
 VAR MaxMonthNumber = MONTH(CALCULATE(MAX('Calendar'[Date]),ALLSELECTED('Calendar')))

 RETURN
 SUMX(
    ADDCOLUMNS(
        SUMMARIZE('Data Table','Data Table'[PERIOD_YEAR]),
        "Qty",
        IF('Data Table'[PERIOD_YEAR]=MaxYear,
        DIVIDE([Total Quantity],MaxMonthNumber)*12,
        [Total Quantity]        
        )
    ),
    [Qty]
 )

[danextian]

Hi @JST15 

Are you trying to create a calculated table? SUMX returns a scalar value which is not a valid a table. That is supposed to be inputed as a measure. Add th year from your calendar table to a viz and then add this measure.

[JST15]

It's wortking. Thank you again!!!

[Bibiano_Geraldo]

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

[JST15]

Sorry, the volume amount of 707 in the table is the amount through October and not through today like I said in the original post.