Cant Add Prior Year Revenue by Account to Matrix

[DannyMiletic]

Hi - I need help with the right dax measure/column to get the PY Actuals to report correctly in this matrix with the Actuals slicer.

I got the prior year Actuals in the Actuals Table and can see the correct prior year amount correctly but can't get it correct added as  a column in the report. Also why is the total for PY_Actuals showing the sae amount as th elast line and why doesn't it sum correctly? Any help appreciated here.

[DannyMiletic · Accepted solution]

Also can's seem to get the current year and PY actuals by date to summarize into total aggregates by month.

[DannyMiletic · Accepted solution]

Also can's seem to get the current year and PY actuals by date to summarize into total aggregates by month.

[v-yiruan-msft · Accepted solution]

Hi @DannyMiletic ,

Please follow the steps below and check if it can return your expected result.

1. Update the formula of measure [PY Actuals] as below

PY Actuals =
VAR _seldate =
    SELECTEDVALUE ( Actuals[Date] )
VAR _selamount =
    SELECTEDVALUE ( Actuals[Account] )
VAR _pre =
    DATE ( YEAR ( _seldate ) - 1, MONTH ( _seldate ), DAY ( _seldate ) )
RETURN
    CALCULATE (
        SUM ( Actuals[Value] ),
        FILTER (
            ALLSELECTED ( Actuals ),
            Actuals[Date] = _pre
                && Actuals[Account] = _selamount
        )
    )

2. Create a measure as below base on the measure [PY Actuals] and put this new measure on your matrix visual to replace the original measure [PY Actuals]

Measure =
SUMX (
    GROUPBY ( Actuals, Actuals[United States], Actuals[Account] ),
    [PY Actuals]
)

Best Regards