Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Aggregation for Same Period Last Year

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's avatar
    Anonymous
    4 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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