Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

SUM multiple month values over years

I'm trying to sum multiple month values over several years e.g. Jan 2007, Jan 2008, Jan 2009.   I'm using the following formula however when I try push the date forward 12 months it comes back to t...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    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

v-janeyg-msft's avatar
v-janeyg-msft
Icon for Community Support rankCommunity Support
4 years ago

Anonymous  What is the logic of the maximum? The yellow part you marked is not the maximum. What should the result be.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.

 200720082009Total Column
Jan794248.24656766.69580901.052031915.98
Feb891135.91600080.00622581.142113797.05
Mar961289.24559538.52496137.872016965.62
Apr1128104.82999667.17678893.222806665.2
May936192.74893231.961067165.232896589.93
Jun982304.46845141.60872586.22700032.26
Jul922542.98890547.411068396.582881486.97
Aug952834.59721560.95835707.462510103.00
Sep1009868.98963437.23709610.402682916.61
Oct914273.54719792.99806738.222440804.75
Nov825601.871156109.32868164.012849875.20
Dec991548.75921709.14746933.502660191.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

 

 2007Total Column
Jan794248.242031915.98
Feb891135.912113797.05
Mar961289.242016965.62
Apr1128104.822806665.2
May936192.742896589.93
Jun982304.462700032.26
Jul922542.982881486.97
Aug952834.592510103.00
Sep1009868.982682916.61
Oct914273.542440804.75
Nov825601.872849875.20
Dec991548.752660191.40
  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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