Forum Discussion
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...
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]
)