Difference of Two column

[RAKESH1986]

Hi All,

@Anonymous 

I am new to power bi, so need your help to solve my below problem.

PW attribute is my week no and the corresponding value in the PW value field...PW values are duplicate --each date has this value

2. here you can see I am getting a sum of these values in the matrix table.

3. now I need to show the difference between each week's value

PW01 = 0

PW02= 1.23-.48

PW03= 2.-1.3

Could you help me how to solve this...this new value needs to be stored in another column either in the same or a new table...new measure or column does not matter.

pls, help.

thanks

[MFelix · Accepted solution]

Hi @RAKESH1986 ,

Best option is to create a column with only the week number and add the following measure to your model:

Previous Week =
VAR PWVALUESSELECTD =
   MAX ( 'Table'[PwAttribute] ) 
RETURN
    SUM ( 'Table'[Value] )
        - SUMX (
            FILTER ( ALL ( 'Table' ), 'Table'[PwAttribute] = PWVALUESSELECTD - 1 ),
            'Table'[Value]
        )

If you want to keep the format try the following code:

Previous Week =
VAR PWVALUESSELECTD =
    RIGHT ( MAX ( 'Table'[PwAttribute] ), LEN ( MAX ( 'Table'[PwAttribute] ) ) - 2 )
RETURN
    SUM ( 'Table'[Value] )
        - SUMX (
            FILTER ( ALL ( 'Table' ), 'Table'[PwAttribute] = "PW" & PWVALUESSELECTD - 1 ),
            'Table'[Value]
        )

[HiraNegi · Accepted solution]

Hi,

You may create measure like below.

LastWeekVariance =

VAR vWeekNum = MAX ( MatrixDiff[WeekNum]) - 1

var vRSum = CALCULATE(SUM(MatrixDiff[Value]), ALLEXCEPT(MatrixDiff, MatrixDiff[Attribute]), MatrixDiff[WeekNum] =vWeekNum)

Return

sum(MatrixDiff[Value]) - vRSum

Hope this helps.

[MFelix · Accepted solution]

Hi @RAKESH1986 ,

Best option is to create a column with only the week number and add the following measure to your model:

Previous Week =
VAR PWVALUESSELECTD =
   MAX ( 'Table'[PwAttribute] ) 
RETURN
    SUM ( 'Table'[Value] )
        - SUMX (
            FILTER ( ALL ( 'Table' ), 'Table'[PwAttribute] = PWVALUESSELECTD - 1 ),
            'Table'[Value]
        )

If you want to keep the format try the following code:

Previous Week =
VAR PWVALUESSELECTD =
    RIGHT ( MAX ( 'Table'[PwAttribute] ), LEN ( MAX ( 'Table'[PwAttribute] ) ) - 2 )
RETURN
    SUM ( 'Table'[Value] )
        - SUMX (
            FILTER ( ALL ( 'Table' ), 'Table'[PwAttribute] = "PW" & PWVALUESSELECTD - 1 ),
            'Table'[Value]
        )