cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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_NOMACH_SEQ_NOTRUE_MACH_NOPREV_MACHNEXT_MACH
556927123null64
5569272642364
556927364643001
55692743001643001
556927530013001900
55692769003001null

 

 

Capture.PNG

 

5 REPLIES 5
Highlighted
Community Champion
Community Champion

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
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.

Capture.PNG

Highlighted
Super User II
Super User II

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

Hi @dpiechowski 

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
LinkedIn

Highlighted
Helper I
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.

 

Capture.PNG

 

 

Highlighted
Solution Specialist
Solution Specialist

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.