Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
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!
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.
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] )
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)
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
68 |