Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 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
5 years ago

Hi, Anonymous 

 

Do you want to calculate like below?

You can try:

Aggregation 2 =
SUMX (
    FILTER (
        ALL ( Sales ),
        YEAR ( [Date] ) <= YEAR ( SELECTEDVALUE ( Sales[Date] ) )
            && MONTH ( [Date] ) = MONTH ( SELECTEDVALUE ( Sales[Date] ) )
    ),
    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

    Thank you for the reply v-janeyg-msft . I've tried to incorporate this measure into the sample but no luck. The Sales table only has an 'Sales'[Order Date] and I have been using 'Date'[Date] for the visual above but I can't bring 'Date'[Date]' into the measure:

    Aggregation 2 = 

    SUMX (
     FILTER (
     ALL ( Sales ),
     YEAR ( [Order Date] ) <= YEAR ( SELECTEDVALUE (Sales[Order Date] ) )
     && MONTH ( [Order Date] ) = MONTH ( SELECTEDVALUE ( Sales[Order Date] ) )
     ),
     Sales[Quantity] * Sales[Net Price]
    )
    Do I need to cross filter somehow? From your visual above is there a pbix you can share?
     
     

     

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

      Hi, Anonymous  

       

      The picture is yours, I just ask about your needs. You didn’t provide a sample file. How could I have it? I iust wrote the code according to your description.

      If you still need help, please upload some insensitive data samples and expected output, So we can help you soon.

       

      Best Regards,

      Community Support Team _ Janey