Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
71 | |
49 | |
45 | |
20 | |
17 |