Forum Discussion
Combining two columns after the latest value
Hello,
I have two columns and I want to combine these two columns after the latest value. Could you please help me?
Example:
| Date | Column A | Column B | Combined Column A and B |
| 2022-01-01 | 100 | null | null |
| 2022-01-02 | 150 | null | null |
| 2022-01-03 | 150 | null | null |
| 2022-01-04 | 250 | 750 | 750 |
| 2022-01-05 | 200 | 200 | |
| 2022-01-06 | 50 | 50 |
This calculates the max date of column B and then uses it till it gets to the date and then to A. Snippet and sample file is also attached.
A+B = VAR MaxDateB = CALCULATE ( MAX ( 'Table'[Date] ), FILTER ( 'Table', NOT ( ISBLANK ( 'Table'[B] ) ) ) ) RETURN IF ( 'Table'[Date] <= MaxDateB, 'Table'[B], 'Table'[A] )
4 Replies
- moizsherwani
Continued Contributor
Hi jacko11
It isn't clear what you mean by combine the two column after the latest value? From the snippet example it seems like you are taking Column B as a priority and then when there is nothing in column B then you take column A, is this correct?
If that is the case you would simple do something like
Combined Column A and B = If(NOT(ISBLANK(Column B)),Column B, Column A) Hi moizsherwani,
Thank you for your answer.
I meant till to the last value on column B, it should get the values from column B which 750 in the example, then column A.
- moizsherwani
This calculates the max date of column B and then uses it till it gets to the date and then to A. Snippet and sample file is also attached.
A+B = VAR MaxDateB = CALCULATE ( MAX ( 'Table'[Date] ), FILTER ( 'Table', NOT ( ISBLANK ( 'Table'[B] ) ) ) ) RETURN IF ( 'Table'[Date] <= MaxDateB, 'Table'[B], 'Table'[A] )
Thank you it worked!
