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 the orignial value e.g. (Jan 2007) + Jan 2007 instead of (Jan 2007 + Jan 2008)

 

Is there a way to achieve multiple Jan additions?

 

I am using Alberto Ferrari's "Rolling Average" file as a sample template

 

My DAX is

Aggregation 2 =
CALCULATE (
 SUMX (
    Sales,
    Sales[Quantity] * Sales[Net Price]
 ),
 ALLEXCEPT (
    'Date',
    'Date'[Month]
 ),DATESINPERIOD('Date'[Date],FIRSTDATE('Date'[Date]),1,MONTH))

+

CALCULATE (
 SUMX (
    Sales,
    Sales[Quantity] * Sales[Net Price]
 ),
 ALLEXCEPT (
    'Date',
    'Date'[Month]
 ),DATESINPERIOD('Date'[Date],FIRSTDATE('Date'[Date]),1,MONTH),EDATE('Date'[Date],12))
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

14 Replies

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    4 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Create a measure based on month not at all the date. it will work I had the same issue.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Good advice. Tried this but still the same Sales vs. Date table issue.

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

    Hello 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

    • 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.

  • 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)