cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Prodigy

## Calculating difference in table in Power BI

Hello,

I have an issue calculating difference between values in different columns and different rows in same table. My data table example:

My query in DAX:

Column =
VAR _0 = MAXX(FILTER('x','x'[date]<EARLIER('x'[date]) && 'x'[ID]= EARLIER('x'[ID])),[date])
VAR _1 = MAXX(FILTER('x','x'[date] =_1 && 'x'[ID]= EARLIER('x'[ID]) ),[cre])
return
if('x'[deb] <> 0,_1 - 'x'[deb], blank())

My expected result is:

But I am getting result that:

So what's wrong with my query in DAX? How to solve my issue?

0.87!=-3289.13

1 ACCEPTED SOLUTION
Community Support

Hi, @Analitika

if('x'[deb] <> 0,_1 - 'x'[deb], blank())

I guess you mean DET here.

Try this:

``````Column2 =
VAR _lastDate =
MAXX (
FILTER (
'Table',
'Table'[ID] = EARLIER ( 'Table'[ID] )
),
[DATE]
)
VAR _1 =
MAXX (
FILTER ( 'Table', 'Table'[DATE] = _lastDate && 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
[CRE]
)
var _if=
IF ( 'Table'[DET] <> 0, _1 - 'Table'[DET], BLANK () )
return _if``````

Result:

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Community Support

Hi, @Analitika

if('x'[deb] <> 0,_1 - 'x'[deb], blank())

I guess you mean DET here.

Try this:

``````Column2 =
VAR _lastDate =
MAXX (
FILTER (
'Table',
'Table'[ID] = EARLIER ( 'Table'[ID] )
),
[DATE]
)
VAR _1 =
MAXX (
FILTER ( 'Table', 'Table'[DATE] = _lastDate && 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
[CRE]
)
var _if=
IF ( 'Table'[DET] <> 0, _1 - 'Table'[DET], BLANK () )
return _if``````

Result:

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

Hey @Analitika ,

I assume you have to change this line

``VAR _1 = MAXX(FILTER('x','x'[date] =_1 && 'x'[ID]= EARLIER('x'[ID]) ),[cre])``

to this

``VAR _1 = MAXX(FILTER('x','x'[date] =_0 && 'x'[ID]= EARLIER('x'[ID]) ),[cre])``

Hopefully, this provides what you are looking for.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Post Prodigy

I have changed it before but still problem is not solved:

Column =
VAR _0 = MAXX(FILTER('x','x'[date]<EARLIER('x'[date]) && 'x'[ID]= EARLIER('x'[ID])),[date])
VAR _1 = MAXX(FILTER('x','x'[date] =_0 && 'x'[ID]= EARLIER('x'[ID]) ),[cre])
return
if('x'[deb] <> 0,_1 - 'x'[deb], blank())

My expected result:

My result which is wrong:

0.87!=-3289.13

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors