Trying to calculate percentage change with previous row

[rischav]

Hey everyone, 

I'm trying to calculate the percentage change compared to the previous row for the expenditures column. This would a column next to the last column. so row 2 would have the percentage change between row 1 and 2. 

Background on the index column: The index column is created for a months column but I could not convert the months column into a date column since there are some years when there is a 13th and 14th month for some settlements and power Bi would not recognize that since a year can't have more than 12 months. 

Background on the expenditures column: Please note, the expenditures column is a measure that is a sum of expenses for multiple conditions on account numbers. I hope this makes sense. Looking forward to hearing from someone. 

[AlB · Accepted solution]

Hi @rischav 

If you are using the [Expenditures total amount] column in a table visual with ID and Index in rows, as you seem to be showing, then use this measure:

Measure =
DIVIDE (
    [Expenditures total amount],
    CALCULATE (
        [Expenditures total amount],
        FILTER (
            ALL ( Table1[Index] ),
            Table1[Index]
                = ( SELECTEDVALUE ( Table1[Index] ) - 1 )
        )
    )
)

and format it as % if necessary

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[MichaelDaSIlva]

Hi, Im very new to all this. I've created my own table to mimic the example above, yet the measure is syntactially incorrect. "Cannot Find Name Expenditures Total Amount".  Do you have to wrap those columns into some function to make this work?

[rischav]

@AlB You're a savior! Thanks a lot for the help

[rischav]

@AlB 

One quick follow up question. There is also a month column that has all the months January, february and so on..... based on the index. when I add the month column to the table visual, the calculation disppears. Is there a fix for that? 

Also, I added the month as a slicer and when I select a month, the % change disppears. 

[AlB]

@rischav 

I would need some more info. If the % disappears, it is most likely because the  SELECTEDVALUE ( Table1[Index] ) is returning a blank, which means we have more than one value for Table1[Index] in the filter context. Could that be the case? Also, if you are adding fields to the visual we probably have to take them into account in the measure, since we want to have the value of the previous line and that might have a different value for those fields (just as it happens with Index in the current version of the measure). Can you show at least what the visual would look like in this case, so that i can have an idea of what I was describing above?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[rischav]

Hi @AlB 

Thanks for getting back and I apologize for the late reply. The table visual looks likes this where I've added the % change based on the DAX formula you provided. The screenshot provided below shows the table and when I bring in the month column alongwith the index column, the %change disappears. the index has 13 counts since we have a 13th month called the adjustment month and hence i cannot use this as a date/month column. 

[AlB]

@rischav 

If I understand correctly what you are doing, we need to account for the additional fields you are including in the visual and that will be part of the filter context:

Measure =
DIVIDE (
    [Expenditures total amount],
    CALCULATE (
        [Expenditures total amount],
        FILTER (
            ALL ( Table1[Index] ),
            Table1[Index]
                = ( SELECTEDVALUE ( Table1[Index] ) - 1 )
        ),
       ALLEXCEPT(Table1, Table1[Program])
    )
)

This actually assumes the previous item of interest has always the current index -1 as index, and that you want to look at the change within the current Program. Otherwise more changes will be necessary

If you share the pbix (privately if necessary) or a mock version that reproduces the issue, it will be easier to work out the solution 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers