Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
jacko11
Frequent Visitor

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 AColumn BCombined Column A and B
2022-01-01

100

nullnull
2022-01-02150nullnull
2022-01-03150nullnull
2022-01-04250750750
2022-01-05200 200
2022-01-0650 50
1 ACCEPTED SOLUTION
moizsherwani
Continued Contributor
Continued Contributor

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

 

moizsherwani_0-1647786739363.png

 

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.

View solution in original post

4 REPLIES 4
jacko11
Frequent Visitor

Thank you it worked! 

jacko11
Frequent Visitor

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
Continued Contributor
Continued Contributor

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

 

moizsherwani_0-1647786739363.png

 

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.
moizsherwani
Continued Contributor
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)

 

 

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors