topic Re: undefined in DAX Commands and Tips

undefined

Anonymous — Mon, 22 Jun 2020 01:13:20 GMT

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

Re: undefined

vivran22 — Mon, 22 Jun 2020 01:34:46 GMT

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

Re: undefined

Anonymous — Mon, 22 Jun 2020 01:42:38 GMT

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

Expected result

Date Result

19/1/2020. -12

20/1/2020. 20

Re: undefined

vivran22 — Mon, 22 Jun 2020 02:32:12 GMT

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

Re: undefined

stevedep — Mon, 22 Jun 2020 04:38:54 GMT

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.