topic Re: Aggregation for Same Period Last Year in DAX Commands and Tips

Aggregation for Same Period Last Year

Anonymous — Thu, 28 Apr 2022 07:49:13 GMT

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

Re: Aggregation for Same Period Last Year

Anonymous — Thu, 28 Apr 2022 08:56:35 GMT

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?

Re: Aggregation for Same Period Last Year

Anonymous — Tue, 03 May 2022 05:51:22 GMT

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