Tag rows based on field values in different rows

[Anonymous]

Hello experts

I need some help with a specific task. Currently I am trying to put financial data into context. To do this, we load different Excel files with the same structure into a spreadsheet twice a month.

The table consists of the following fields (blue fields is what I have, orange field is what I need to get the magenta values):
- key date
- plan (investment plan; investmentA changes if the plan changes)
- investmentA (shows the initial investment)
- investmentB (shows the current value of the initial investment)

- tag (the field am trying to generate)

This results in the following output:

key date	plan	investmentA	investmentB	tag	finalA	finalB
2022-01-15	A	90'000	88'000	1
2022-01-31	A	90'000	86'000	1
2022-02-15	B	86'000	84'000	1
2022-02-28	B	86'000	82'000	1
2022-03-15	B	86'000	80'000	1
2022-03-15	B	10'000	9'800	2
2022-03-31	B	86'000	81'000	1	90'000	81'000
2022-03-31	B	10'000	9'900	2	10'000	9'900

Initially, there is only one record per date, because an initial investment was made on January 1. On February 15, a plan change took place, which caused the value in column investmentA to adjust to the value of column investmentB from the previous key date. This link is the only relationship between the 86'000 and the 90'000. Without this relationship it is not possible to say with certainty which investments from plan B belong to plan A (this is of course a simplified example). In the end, I am looking for finalA and finalB.

I know how to calculate finalA and finalB if I had the tag column. But my question is how to generate the tag column.

I hope that my requirement is somewhat understandable.

Greetings

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

I don't quite understand why all the others are 1 and only those two rows are 2. All I can see is that they are 10000 or they are no equal to 86000?

Try this:

Column =
VAR _first_plan =
    MAXX ( TOPN ( 1, 'Table', [key date], ASC ), [plan] )
VAR _last_a_invb =
    MAXX (
        TOPN ( 1, FILTER ( 'Table', [plan] = _first_plan ), [key date], DESC ),
        [investmentB]
    )
RETURN
    IF ( [plan] <> _first_plan, IF ( [investmentA] = _last_a_invb, 1, 2 ), 1 )

Rsult:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi experts

In the meantime, I have found a solution. It is not very robust, because there can be several changes of plan. In principle, two more columns have to be added for each plan change. Since there is no case in my scenario where there was more than one plan change, I simply calculated with a maximum of 3 plan changes (i.e. 6 more columns).

The first column contains the following formula:

=CALCULATE(
FIRSTNONBLANK([investmentA],1),
FILTER('table',[investmentB]=EARLIER([investmentA])))

 The above formula gives 90,000 for each line where 86,000 is at investmentA. Other rows of the same column stay empty. Therefore I added a second column, which fetches the value from investmentA, if there is no value in the new column. Otherwise the value from the new column is displayed in the second column.

The second column, which is now completely filled, contains my total if there was only one change of plan. Lets call this column total1. Now the third column contains the above formula again but with a slight adjustment.

=CALCULATE(
FIRSTNONBLANK([investmentA],1),
FILTER('table',[investmentB]=EARLIER([total1])))

Instead of investmentA I now search total1. However, the procedure is analogous to the one described above. At the end my result is in the column total3.

If someone has a better solution suggestion, I would of course be interested.

Greetings