Forum Discussion

obesli's avatar
obesli
Frequent Visitor
8 years ago
Solved

Calculating cumulative values

 

 

I have data lite this table. Year mont column and sales amount in each mont. I would like to calculate mothly cumulative value, but I couldn't do it. IS there any method to propose?

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi obesli,


    You can try to use below measures:

    Spoiler
    Cumulative = 
VAR _current =
    SELECTEDVALUE ( 'Table'[DateKey] )
VAR _previous =
    MAXX ( FILTER ( ALL ( 'Table' ), [DateKey] < _current ), [DateKey] )
RETURN
    IF (
        RIGHT ( VALUE ( _current ), 2 ) <> "01",
        SUMX (
            FILTER (
                ALL ( 'Table' ),
                [DateKey] < _current
                    && LEFT ( VALUE ( [DateKey] ), 4 ) = LEFT ( VALUE ( _current ), 4 )
            ),
            [Sales]
        ),
        MAX ( 'Table'[Sales] )
            + LOOKUPVALUE ( 'Table'[Sales], 'Table'[DateKey], _previous )
    )


Cumulative(Jan replace current + Previous Total) = 
VAR _current =
    SELECTEDVALUE ( 'Table'[DateKey] )
VAR _previous =
    MAXX ( FILTER ( ALL ( 'Table' ), [DateKey] < _current ), [DateKey] )
RETURN
    IF (
        RIGHT ( VALUE ( _current ), 2 ) <> "01",
        SUMX (
            FILTER (
                ALL ( 'Table' ),
                [DateKey] < _current
                    && LEFT ( VALUE ( [DateKey] ), 4 ) = LEFT ( VALUE ( _current ), 4 )
            ),
            [Sales]
        ),
        MAX ( 'Table'[Sales] )
            + SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[DateKey] <= _previous
                        && LEFT ( VALUE ( [DateKey] ), 4 ) = LEFT ( VALUE ( _previous ), 4 )
                ),
                [Sales]
            )
    )

    Result:

     

    Regards,

    Xiaoxin Sheng

8 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    HI obesli

     

    You can use this calculated column

     

    But this will Cumulate from all prior year.

    Do you want accumulation to restart every year

     

    =
CALCULATE (
    SUM ( [Total Sales] ),
    FILTER ( Table1, Table1[Year Month] <= EARLIER ( Table1[Year Month] ) )
)
    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      obesli

       

      You can try using this column if you Cumulative results to restart each year

       

      =
CALCULATE (
    SUM ( [Total Sales] ),
    FILTER (
        Table1,
        LEFT ( Table1[Year Month], 4 ) = LEFT ( EARLIER ( Table1[Year Month] ), 4 )
            && Table1[Year Month] <= EARLIER ( Table1[Year Month] )
    )
)

       

       

      • obesli's avatar
        obesli
        Frequent Visitor
        8 years ago

        Hi,

         

        Thank you for yoru reply. I did the second query and it works weel, but I need additional support.

        I would like to summarize values before 201801 under 201801 and after 201901 under 201901.

         

        How can I add such additional query?

         

        Thanks,