Aggregation for Same Period Last Year

[Anonymous]

Hi Everyone,

I have an aggregate measure accumulating the sales as the months go on called "Actual TRG AGG"

I've highlighted above the portion relating to the Aggregating element but when I try to create the same measure for the year before:

You can see that I'm not able to maintain the aggregating element.. how do I keep the aggregation in the -1 measure?

Thanks,

Eyal

[Anonymous · Accepted solution]

Hi @Anonymous ,

Do you mean that the calculation of [Actual TRG AGG-1] is wrong and not cumulative? Please update its formula as below and check whether it can return the correct result...

Actual TRG AGG-1 =
VAR _selyear =
    SELECTEDVALUE ( 'Calendar'[Date].[Year] )
VAR _selmonth =
    SELECTEDVALUE ( 'Calendar'[Date].[MonthNo] )
RETURN
    CALCULATE (
        [Actual TRG],
        FILTER (
            ALLSELECTED ( 'SALES' ),
            'SALES'[LOB] = SELECTEDVALUE ( 'SALES'[LOB] )
                && YEAR ( 'SALES'[Date] ) = _selyear - 1
                && MONTH ( 'SALES'[Date] ) <= _selmonth
        )
    )

If the above one can't help you get the desired result, please provide some sample data in your tables SALES, Conversion_Table_TRG and Territory3 (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous]

I know it has to do with that ALLSELECTED which keeps me on the selected 2022 and for Jan,Feb and Mar... but I need to keep it there but perhaps add an except element for the year?