cancel
Showing results for
Did you mean:
Highlighted
Helper I

## 2 Calculated columns, return next value and previous value

I need to create a pair of calculated columns that return the next and previous TRUE_MACH_NO of each grouped ORDER_NO & SPEC_NO determined by MACH_SEQ_NO. Sometimes the TRUE_MACH_NO is the same twice in a row because the product does have to go through the same machine twice, but this is reflected in the table as an incremented MACH_SEQ_NO. The MACH_SEQ_NO sometimes has decimals (I don't know why) and sometimes even goes negative (I REALLY don't know why)...

 SPEC_NO MACH_SEQ_NO TRUE_MACH_NO PREV_MACH NEXT_MACH 556927 1 23 null 64 556927 2 64 23 64 556927 3 64 64 3001 556927 4 3001 64 3001 556927 5 3001 3001 900 556927 6 900 3001 null

5 REPLIES 5
Highlighted
Super User V

## Re: 2 Calculated columns, return next value and previous value

here are some examples of possible solutions.

``````Prev =
var sn='Table (4)'[SPEC_NO]
var ms='Table (4)'[MACH_SEQ_NO]-1
var m=CALCULATE(max('Table (4)'[TRUE_MACH_NO]),all('Table (4)'),treatas({(sn,ms)},'Table (4)'[SPEC_NO],'Table (4)'[MACH_SEQ_NO]))
return m

Next =
var sn='Table (4)'[SPEC_NO]
var ms='Table (4)'[MACH_SEQ_NO]+1
var m=CALCULATE(max('Table (4)'[TRUE_MACH_NO]),all('Table (4)'),'Table (4)'[SPEC_NO]=sn,'Table (4)'[MACH_SEQ_NO]=ms)
return m``````
Highlighted
Helper I

## Re: 2 Calculated columns, return next value and previous value

This seems to work really well except for the non-integer MACH_SEQ_NO rows, since var ms is subtracting 1 from itself it will miss any non-standard steps in the sequence. This is a good start though! Thank you.

Highlighted
Super User VI

## Re: 2 Calculated columns, return next value and previous value

try

``````Prev_Mach =
var _curMACH_SEQ_NO = [MACH_SEQ_NO]
var _prevMACH_SEQ_NO = CALCULATE(MAX(Table[MACH_SEQ_NO], ALLEXCEPT(Table, Table[ORDER_NO], Table[SPEC_NO]), Table[MACH_SEQ_NO] <  _curMACH_SEQ_NO )
RETURN
CALCULATE(MAX(Table[TRUE_MACH_NO]), ALLEXCEPT(Table, Table[ORDER_NO], Table[SPEC_NO]), Table[MACH_SEQ_NO] = _prevMACH_SEQ_NO)``````

and

``````Prev_Mach =
var _curMACH_SEQ_NO = [MACH_SEQ_NO]
var _nextMACH_SEQ_NO = CALCULATE(MIN(Table[MACH_SEQ_NO], ALLEXCEPT(Table, Table[ORDER_NO], Table[SPEC_NO]), Table[MACH_SEQ_NO] > _curMACH_SEQ_NO )
RETURN
CALCULATE(MAX(Table[TRUE_MACH_NO]), ALLEXCEPT(Table, Table[ORDER_NO], Table[SPEC_NO]), Table[MACH_SEQ_NO] = _nextMACH_SEQ_NO)``````

do not hesitate to give a kudo to useful posts and mark solutions as solution
Highlighted
Helper I

## Re: 2 Calculated columns, return next value and previous value

Thank you. I think this is the right way to go, but I can't seem to get the syntax correct. It says 'Unexpected Parameter' and I'm not quite sure how to adjust it to clear the error.

Solution Sage

## Re: 2 Calculated columns, return next value and previous value

Before writing DAX, you really should UNDERSTAND your data... If you don't know why some numbers are decimal... well, that spells troubles.

Best
D

Announcements

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### August Community Highlights

Check out a full recap of the month!

#### Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors
Top Kudoed Authors