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

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's avatar
obesli
Frequent Visitor
8 years ago

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?

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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