Forum Discussion
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. 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!!
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] )
6 Replies
Hi JST15 , please try the following DAX:
1- Annualizing the 2024
AnnualizedVolume2024 = VAR YTDVolume = CALCULATE(SUM(YourTable[Volume]), YourTable[Year] = 2024) RETURN (YTDVolume / 10) * 122. 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) ) - 1I hope this help you, if yes, please give kudo and mark as solution.
Thank you
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] )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] )
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.
