Forum Discussion

tomislav_mi's avatar
tomislav_mi
Helper II
6 years ago
Solved

Calculating a difference between two rows as a calculated column

Hey guys, Could you please help me out to get the difference between rows as a calculated column considering a few parameters? I would need to calculate the difference between sales, having in ...
Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Probably something like below. PBIX attached.

     

    Column = 
    VAR __Previous = 
        MAXX(
            FILTER(
                ALL('Table'),
                [Account] = EARLIER([Account]) &&
                    [Date] < EARLIER([Date])
            ),
            [Date]
        )
RETURN
    [Sales] - 
    MAXX(
        FILTER(
            ALL('Table'),
            [Account] = EARLIER([Account]) &&
            [Date] = __Previous
        ),
        [Sales]
    )

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Probably something like below. PBIX attached.

 

Column = 
    VAR __Previous = 
        MAXX(
            FILTER(
                ALL('Table'),
                [Account] = EARLIER([Account]) &&
                    [Date] < EARLIER([Date])
            ),
            [Date]
        )
RETURN
    [Sales] - 
    MAXX(
        FILTER(
            ALL('Table'),
            [Account] = EARLIER([Account]) &&
            [Date] = __Previous
        ),
        [Sales]
    )

 

tomislav_mi's avatar
tomislav_mi
Helper II
6 years ago

Thank you Greg_Deckler .

Works perfectly in PowerBI. I also use same Excel data model and when I try to use the same approach in Power Pivot it doesn't work there. There it calculates the difference only for the first row?

Do you have any idea why this could be the case? 

Tomislav