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

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,

 

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi obesli

     

    Please could you show the desired results?

    • obesli's avatar
      obesli
      Frequent Visitor
      8 years ago

      I would like to show data like this.

       

       

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        HI obesli

         

        In that case you can add a colum to Group the periods

        Then use this column for chart

        For example

         

        =
IF (
    Table1[Year Month] > 201901,
    "2019-2020",
    FORMAT ( Table1[Year Month], "General Number" )
)