Forum Discussion

gopi1's avatar
gopi1
New Member
7 years ago
Solved

Subtract two columns in a table

Hi,

 

I need to subtract two columns in a table. If the user chooses any month from filter then calculated field should show me the difference. Attached picture for reference.

 

Need Help
Show and Tell
  • v-xuding-msft's avatar
    v-xuding-msft
    7 years ago

    Hi gopi1 ,

    I created a sample you can have a try.

    • Create a calendar table and get the month of it.  

     

    Table 2 = CALENDARAUTO()
Month = MONTH('Table 2'[Date])
    • Create a many-to-one relationship between two tables.

    • create a measure

     

     

    Measure = 
var a = SELECTEDVALUE('Table 2'[Month])
var b = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Column] = a && 'Table'[Month] = MAX('Table'[Month])))
var c = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Column] = a && 'Table'[Month] = MAX('Table'[Month])-1))
return
IF(ISFILTERED('Table 2'[Month]), b-c, SUM('Table'[Value]))

    Here is my sample that you can download.

     

3 Replies

    • gopi1's avatar
      gopi1
      New Member
      7 years ago

      In below function they are pre-defining the values but in my case the month will be in filter. User can choose any months from the filter.

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    7 years ago

    Hi gopi1 ,

    I created a sample you can have a try.

    • Create a calendar table and get the month of it.  

     

    Table 2 = CALENDARAUTO()
Month = MONTH('Table 2'[Date])
    • Create a many-to-one relationship between two tables.

    • create a measure

     

     

    Measure = 
var a = SELECTEDVALUE('Table 2'[Month])
var b = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Column] = a && 'Table'[Month] = MAX('Table'[Month])))
var c = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Column] = a && 'Table'[Month] = MAX('Table'[Month])-1))
return
IF(ISFILTERED('Table 2'[Month]), b-c, SUM('Table'[Value]))

    Here is my sample that you can download.