Forum Discussion
Aggregation for Same Period Last Year
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.
Best Regards
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.
Best Regards