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...
  • danextian's avatar
    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]
        )