cancel
Showing results for
Did you mean:
Frequent Visitor

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

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

2 ACCEPTED SOLUTIONS
Super User III

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]) ``

Proud to be a Super User!

Super User III

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])
)
)``````

Proud to be a Super User!

9 REPLIES 9
Frequent Visitor

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

Best regards

Hansson

Super User III

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])
)
)``````

Proud to be a Super User!

Frequent Visitor

 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

Super User III

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

do you have an example ?

Proud to be a Super User!

Frequent Visitor

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.

Best regards

Hansson

Frequent Visitor

@camargos88 thanks that worked perfectly.

Best regards

Hansson

Super User III

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]) ``

Proud to be a Super User!

Super User III

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}``````

Proud to be a Super User!

Frequent Visitor

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

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!