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

 

 

  • 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.

5 Replies

  • Jcastill , are you looking for column calculations. MOM , I doubt that is possible. You have to use Month formula and MOM to get this.

    refer: https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
    last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
    last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
    last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
    last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
    Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
    Next month value =  CALCULATE(sum('table'[total hours value]),nextmonth('Date'[Date]))
    
    last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))
    MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
    MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))
    	
    
    
    diff = [MTD Sales]-[last MTD Sales]
    diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

    See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.

  • 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

      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
      😞
       
       
       

       

       

       

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support

    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.