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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors