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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Anonymous , with help from date table, You can try like

example

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks amitchandak but it's not quite what I'm looking for.

     

    I'm trying to group all of them together rather than just obtain a 'Date'[Date],-1,YEAR)