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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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