Forum Discussion

Jcastill's avatar
Jcastill
Regular Visitor
6 years ago
Solved

subtraction same colum

Hello Community, someone can help me. I am nes in BI how can I do a subtract between 2 values on the same column (Matrix chart) here my data in the matrix cha    
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    Hi Jcastill ,

     

    You need a calendar table and create relationships with the fact table.

    Then create measure like this:

    Subtract Previous Year = 

var VarThis = SUM('Table'[value])

var LastYear =CALCULATE(SUM('Table'[value]),dateadd('Table 2'[date],-1,YEAR))

return

IF(
VarThis - LastYear = 0, VarThis, VarThis - LastYear)

    Sample .pbix

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

westwrightj's avatar
westwrightj
Resolver III
6 years ago

Hey Jcastill 

 

   Thanks for writing a post. I think I've got a solution to your issue, but without the underlying data I can't say for certain if this will work for you

 

First I've started out with these test data based on your snippet

 

 

Next I created a measure to take the max value and subtract if the previous years value. If the value is 0, we are going to show it as blank.

 

Subtract Previous Year = 

var VarThis = CALCULATE(MAX('Test Data'[Value]))

var LastYear = CALCULATE(MAX('Test Data'[Value]), 'Test Data'[Year] - 1)

return

IF(
VarThis - LastYear = 0, BLANK(), VarThis - LastYear)

 

Once I throw that value into the matrix, I added some condition formatted (I didn't choose visually appealing colors and I apologize for that blunder)

 

 

In this case we have a column for the actual value and to the right a column indicating the change for that month from the previous year.

 

Let me know if solution works for you or if we want to try and think of something else!

 

  • Jcastill's avatar
    Jcastill
    Regular Visitor
    6 years ago

    Hello,

    thank you for your help. but I have the same problem.

    I create a measure :

    Subtract Previous Year =

    var VarThis = CALCULATE(MAX(VW_ARAGING[Amount OVerdue]))

    var LastYear = CALCULATE(MAX(VW_ARAGING[Amount OVerdue]), VW_ARAGING[Eff date].[Year] - 1)

    return

    IF(
    VarThis - LastYear = 0, BLANK(), VarThis - LastYear)
     
    but the result is blank
    😞