Forum Discussion

lab's avatar
lab
Frequent Visitor
5 years ago
Solved

Subtracting a filter value

Hi !

I am trying to create a measure which subtracts filtered value based on month (Current month - previous month.)

Sum of the Actual Value for June where Type = "$" - Sum of the Actual Value for Previous Month (May) where Type = "Est"

i.e.  3,300 - 1,600=700

 

SegmentCountry Product  Discount Band Value Budget/Fcst/Actual  Type DateMonth Number Month Name Year
MidmarketFrance Carretera  None 2500 Actual  $ 6/1/20146 June 2014
MidmarketGermany Carretera  None 800 Actual  $ 6/1/20146 June 2014
MidmarketMexico Carretera  None 1500 Actual  Est. 5/1/20145 May 2014
MidmarketGermany Montana  None 100 Actual  Est. 5/1/20145 May 2014

 

Thank you.

Need Help
  • dkaushik's avatar
    dkaushik
    5 years ago

    Hi lab 

     

    You can try creating following measures:

    CM Value= calculate(sum('TableName'[Value]), 'TableName'[Type] = "$")
    PM Value = calculate(sum('TableName'[Value]), 'TableName'[Type] = "Est.", 'TableName'[Month Number] = 'TableName'[Month Number] - 1)
    Diff Value = [CM Value] - PM Value]

     

    Thanks,

    Dheeraj

2 Replies

  • dkaushik's avatar
    dkaushik
    Resolver II
    5 years ago

    Hi lab 

     

    You can try creating following measures:

    CM Value= calculate(sum('TableName'[Value]), 'TableName'[Type] = "$")
    PM Value = calculate(sum('TableName'[Value]), 'TableName'[Type] = "Est.", 'TableName'[Month Number] = 'TableName'[Month Number] - 1)
    Diff Value = [CM Value] - PM Value]

     

    Thanks,

    Dheeraj