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?
- v-janeyg-msft
Anonymous What is the logic of the maximum? The yellow part you marked is not the maximum. What should the result be.
I'm trying to get the the sum of each month across years (Total Column below(this is what I meant by the max), apologies if I haven't explained it properly.
2007 2008 2009 Total Column Jan 794248.24 656766.69 580901.05 2031915.98 Feb 891135.91 600080.00 622581.14 2113797.05 Mar 961289.24 559538.52 496137.87 2016965.62 Apr 1128104.82 999667.17 678893.22 2806665.2 May 936192.74 893231.96 1067165.23 2896589.93 Jun 982304.46 845141.60 872586.2 2700032.26 Jul 922542.98 890547.41 1068396.58 2881486.97 Aug 952834.59 721560.95 835707.46 2510103.00 Sep 1009868.98 963437.23 709610.40 2682916.61 Oct 914273.54 719792.99 806738.22 2440804.75 Nov 825601.87 1156109.32 868164.01 2849875.20 Dec 991548.75 921709.14 746933.50 2660191.40 I know I have these results in the column [Aggregation] (highlighted above)
Aggregation =
CALCULATE (
SUMX (
Sales,
Sales[Quantity] * Sales[Net Price]
),
ALLEXCEPT (
'Date',
'Date'[Month]
))however when I filter the year e.g. 2007 it remains as these MAX numbers where I would like the ALLEXCEPT function to be a bit more dynamic. So with no date filters applied return [Total Column] and then when I pick the year e.g. 2007 it will change to just those values
2007 Total Column Jan 794248.24 2031915.98 Feb 891135.91 2113797.05 Mar 961289.24 2016965.62 Apr 1128104.82 2806665.2 May 936192.74 2896589.93 Jun 982304.46 2700032.26 Jul 922542.98 2881486.97 Aug 952834.59 2510103.00 Sep 1009868.98 2682916.61 Oct 914273.54 2440804.75 Nov 825601.87 2849875.20 Dec 991548.75 2660191.40 - v-janeyg-msft
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