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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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'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