Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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 4
stevedep
Memorable Member
Memorable Member

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
Community Champion
Community Champion

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
Not applicable

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

 

Expected result

 

Date      Result

19/1/2020.  -12

20/1/2020.  20

@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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.