Forum Discussion
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
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])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]) ) )
9 Replies
camargos88Community Champion
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
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]) ) ) - camargos88
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}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
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
- camargos88
I believe it's necessary to check your tables and cardinality...
do you have an example ?
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 again thank you very much. The calculated column works.
Appreciate your fast response.
Best regards
Hansson
