cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 numberInvoice codeLast StausNext Status
1aaa3040
2bbb4060
3ccc5070
4ddd5050
5aaa6070
6fff3060
7ccc4050
8hhh6080

 

Table 2: The Routing Master for all Invoices

Invoice codeSequence Code
aaa30
aaa40
aaa50
aaa60
aaa70
bbb30
bbb40
bbb60
bbb80
ccc30
ccc40
ccc50
ccc70
ddd40
ddd50
ddd70
fff30
fff60
fff70
hhh40
hhh50
hhh60
hhh80

 

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
Super User III

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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

Super User III
Super User III

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

 

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

9 REPLIES 9
Frequent Visitor

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

 

Appreciate your fast response.

 

Best regards

Hansson

Super User III
Super User III

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

 

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

Frequent Visitor

@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

Super User III
Super User III

@hanssonnor ,

 

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

do you have an example ?



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Frequent Visitor

@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

Frequent Visitor

@camargos88 thanks that worked perfectly.

 

Best regards

Hansson

Super User III
Super User III

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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

Super User III
Super User III

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

 

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



@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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.