How to get the related date for the last non blank value of a another column

[Sica]

Hello!

I have not yet figured out a good way (in both Power Bi Desktop and/or Power Query) to get the date for the last non blank value of another column. In the example below, I would like to get the "Month" for each "Group" for the last know blank value of columns "Forecast 1", "Forecast 2" and "Forecast 3".

By using the colors, I am identifying the values I would like to have for each group and forecast columns. In the end, I would like to be able to get the date values as shown below:

Thank you for any tip you can provide.

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.

[v-zhangti · Accepted solution]

Hi, @Sica 

It's easier to solve your problem with calculated columns.

F1 date = 
CALCULATE (
    MAX ( 'Table'[Month] ),
    FILTER (
        'Table',
        [Group] = EARLIER ( 'Table'[Group] )
            && NOT ( ISBLANK ( 'Table'[Forecast1] ) )
    )
)

F1 value =
IF ( [Month] = [F1 date], [Forecast1], BLANK () )

"Forecast 2" and "forecast 3" also add calculated columns according to the above method.

F2 date =
CALCULATE (
    MAX ( 'Table'[Month] ),
    FILTER (
        'Table',
        [Group] = EARLIER ( 'Table'[Group] )
            && NOT ( ISBLANK ( 'Table'[Forecast2] ) )
    )
)

F2 value =
IF ( [Month] = [F2 date], [Forecast2], BLANK () )

F3 date =
CALCULATE (
    MAX ( 'Table'[Month] ),
    FILTER (
        'Table',
        [Group] = EARLIER ( 'Table'[Group] )
            && NOT ( ISBLANK ( 'Table'[Forecast3] ) )
    )
)

F3 value =
IF ( [Month] = [F3 date], [Forecast3], BLANK () )

The final result is shown in the figure, just as you expected.

Best Regards,

Community Support Team _Charlotte

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

[Sica]

Thank you so much!! 😀

EARLIER did the trick! This I was missing (it is a new function for me).