Calculating cumulative values

[obesli]

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?

[Zubair_Muhammad]

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]

@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]

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]

Hi @obesli

Please could you show the desired results?

[obesli]

I would like to show data like this.

[Zubair_Muhammad]

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

[obesli]

I did it, but this time there are same values in different rows and all off them calculated in the cumulative value. I need to take only row value and the other thing is in 201901 I need to add  the  201812  value to 201901.

How could I solve in the queries?

[v-shex-msft · Accepted solution]

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