Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

undefined

So I habe data like this.

Date. Type Value

19/1/2020 A 12

19/1/2020 B 20

20/1/2020 A 40

20/1/2020 B 20

.

.

I want to calculate A - B and plot it on a graph. I can't write Type = A in a formula to filter as I have many types. But i just want to subtract only two types at once, not more. I tried firstblank and lastblank formula but it doesn't give desire results as it always picks highest and lowest value... I want to make sure I am doing A - B all the time regardless of max or min value. 

any help will highly be appreciated

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Yes every different type (A. B, C) have data for each date.

       

      Expected result

       

      Date      Result

      19/1/2020.  -12

      20/1/2020.  20

      • vivran22's avatar
        vivran22
        Community Champion
        6 years ago

        Anonymous 

         

        You may try this:

        Reuslt = 
VAR _FirstValue = 
 FIRSTNONBLANK(
    dtTable3[Value],
        MAX(dtTable3[Date])
)

VAR _LastValue = 
 LASTNONBLANK(
    dtTable3[Value],
        MAX(dtTable3[Date])
)

VAR _Difference = _FirstValue - _LastValue

RETURN
_Difference

         

        Cheers!
        Vivek

        If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
        If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

        Blog: vivran.in/my-blog
        Connect on LinkedIn
        Follow on Twitter

  • stevedep's avatar
    stevedep
    Memorable Member
    6 years ago

    Hi,

    This is what I have, should be pretty robust. 

     

    Measure = 
var _CT = CALCULATETABLE(SUMMARIZE('Table';'Table'[Type];'Table'[Date]);ALLEXCEPT('Table';'Table'[Date]))
var _AValue = CALCULATE(SUM('Table'[Value]);FILTER(_CT; 'Table'[Type]= "A"))
var _BValue = CALCULATE(SUM('Table'[Value]);FILTER(_CT; 'Table'[Type]= "B"))
return 
_AValue-_BValue

     

    Looking for this? Please mark as solution. Helpful? Thumbs up would be great.

    Kind regards, Steve.