undefined

[Anonymous]

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

[stevedep]

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.  

[vivran22]

Hello @Anonymous ,

What is the expected result? Will your data has two records for each date?

Cheers!
Vivek

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

[Anonymous]

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]

@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