Forum Discussion

RAKESH1986's avatar
RAKESH1986
Icon for Helper II rankHelper II
3 years ago
Solved

Difference of Two column

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

 

 

 

Need Help
  • MFelix's avatar
    MFelix
    3 years ago

    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's avatar
    HiraNegi
    3 years ago

    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.

     

     

     

2 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
    HiraNegi
    Icon for Resolver II rankResolver II
    3 years ago

    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.