Forum Discussion
SUM multiple month values over years
Anonymous
Try:
Aggregation 3 = IF ( ISINSCOPE ( 'Date'[Month] ), CALCULATE ( SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ), ALLEXCEPT ( 'Date', 'Date'[Month] ) ), SUMX ( FILTER ( ALL ( Sales ), YEAR ( [Order Date] ) <= SELECTEDVALUE ( 'Date'[Calendar Year Number] ) ), Sales[Quantity] * Sales[Net Price] ) )Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,Community Support Team _ Janey
Community SupportHello Anonymous
Sorry I'm late.
Accoding to the field used in the matrix visual, I create the measure like this:
Aggregation 3 =
IF (
ISINSCOPE ( 'Date'[Month] ),
SUMX (
FILTER (
ALL ( Sales ),
YEAR ( [Order Date] ) <= SELECTEDVALUE ( 'Date'[Calendar Year Number] )
&& MONTH ( [Order Date] ) = SELECTEDVALUE ( 'Date'[Month Number] )
),
Sales[Quantity] * Sales[Net Price]
),
SUMX (
FILTER (
ALL ( Sales ),
YEAR ( [Order Date] ) <= SELECTEDVALUE ( 'Date'[Calendar Year Number] )
),
Sales[Quantity] * Sales[Net Price]
)
)
The hierarchy context of matrix is different, so the total must be calculated separately, use isinscope() funcion.
Reference:ISINSCOPE function (DAX) - DAX | Microsoft Docs
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
This is excellent thank you very very much for the time spent on this, I'm new to the ISINSCOPE function.
One last question is it possible to tweak the formula to return the max value rather than cumulative?