Calculated Column which measures the next Status based on the last completed one

[hanssonnor]

Hi,

I have a challenging task and currently no idea how to solve it.

I have two tables and in one of the tables, I want to create a new calculated column (Next Status).

Table 1 contains invoices and every invoice has a different status. I already created a calculated column to extract the latest completed status. Now I want to add in the new calculated column the next Status that will follow afterward. Every invoice has a specific routing, this information is available in the other table (table 2).

Table 1: Next Status is the calculated column that I want to create.

Invoice number	Invoice code	Last Staus	Next Status
1	aaa	30	40
2	bbb	40	60
3	ccc	50	70
4	ddd	50	50
5	aaa	60	70
6	fff	30	60
7	ccc	40	50
8	hhh	60	80

Table 2: The Routing Master for all Invoices

Invoice code	Sequence Code
aaa	30
aaa	40
aaa	50
aaa	60
aaa	70
bbb	30
bbb	40
bbb	60
bbb	80
ccc	30
ccc	40
ccc	50
ccc	70
ddd	40
ddd	50
ddd	70
fff	30
fff	60
fff	70
hhh	40
hhh	50
hhh	60
hhh	80

Has anyone andy hint for me how to realize this? I was starting to experiment with the Rank function but came nowhere close to any results. The tables are linked via the invoice code.

Thanks & Best regards

Hansson

[camargos88 · Accepted solution]

@hanssonnor ,

Try this DAX solution for a new calculated column:

Next Status = MINX(FILTER(TBL_2, 'TBL_2'[Invoice code]= EARLIER('Table'[Invoice code]) && TBL_2[Sequence Code] > 'Table'[Last Staus] ), TBL_2[Sequence Code]) 

[hanssonnor]

@camargos88 again thank you very much. The calculated column works.

Appreciate your fast response.

Best regards

Hansson

[camargos88 · Accepted solution]

@hanssonnor ,

Try this new calculated column:

CostCenter = 
CALCULATE(
    DISTINCT(RoutingMaster[Cost Center]),
    FILTER(RoutingMaster, RoutingMaster[Invoice code] = EARLIER('Table'[Invoice code]) && RoutingMaster[Sequence Code] = 
        MINX(FILTER(RoutingMaster, 'RoutingMaster'[Invoice code]= EARLIER('Table'[Invoice code]) && RoutingMaster[Sequence Code] > 'Table'[Last Staus] ), RoutingMaster[Sequence Code]) 
    )
)

[hanssonnor]

@camargos88 

Thanks for your quick reply. The tables look like this:

Invoice code	Sequence Code	Cost Center
aaa	30	GP450
aaa	40	GX350
aaa	50	GB250
aaa	60	GT460
aaa	70	GW230
bbb	30	GP450
bbb	40	GU560
bbb	60	GT460
bbb	80	GK600
ccc	30	GP450
ccc	40	GX350
ccc	50	GB670
ccc	70	GN570
ddd	40	GU560
ddd	50	GB670
ddd	70	GH400
fff	30	GJ300
fff	60	GS350
fff	70	GC220
hhh	40	GX350
hhh	50	GB400
hhh	60	GT460
hhh	80	GK600

the Connection between table 1 to the table above is over a cost center master which contains all cost centers and the description. 

Cost Center	Description
GP450	Test
GX350	Test1
GB250	Test2
GT460
GW230
GP450
GU560
GT460
GK600
GP450
GX350
GB670
GN570
GU560
GB670
GH400
GJ300
GS350
GC220
GX350
GB400
GT460
GK600

Hence the kardinality between the tables looks like this:

Table 1 - Cost Center Master                n:1

Cost Center Master - Routing Master  1:n

I hope that helps.

Best regards

Hansson

[camargos88]

@hanssonnor ,

I believe it's necessary to check your tables and cardinality...

do you have an example ?

[hanssonnor]

@camargos88 

Do you also have an idea, how I can adjust the formula to show me in a new calculated column the associated Cost Center? In The Routing Master, I have as well the Cost Center next to each code sequence.

The Cost Centers are text fields with string format.

My other table is still the same. I initially tried a LOOKUPVALUE(), but there are multiple results, hence this did not work. Maybe you can help me out.

Thanks for your support.

Best regards

Hansson

[hanssonnor]

@camargos88 thanks that worked perfectly.

Best regards

Hansson

[camargos88 · Accepted solution]

@hanssonnor ,

Try this DAX solution for a new calculated column:

Next Status = MINX(FILTER(TBL_2, 'TBL_2'[Invoice code]= EARLIER('Table'[Invoice code]) && TBL_2[Sequence Code] > 'Table'[Last Staus] ), TBL_2[Sequence Code]) 

[camargos88]

@hanssonnor ,

Try this new custom column on Power Query:

let 
_code = [Invoice code],
_status = [Last Staus]
 in

Table.SelectRows(#"Table (2)", each [Invoice code] = _code
and [Sequence Code] > _status)[Sequence Code]{0}

[hanssonnor]

@camargos88thanks for your quick reply.

The [Last Status] is also a calculated column, I cannot choose it in the power query menu.

I have only access to the column of the table I pull out of the ERP system.

Any idea how to bypass that?

Best regards

Hansson