Pick up first non null value if the current value is null or some defined value

[BI_Analyticz]

I have a dataset like below.

I want to check if status is null then pick up the first non-null value for Comp A which is Compliant. 2nd condition - if the value is Non-Compliant then check the value for next month and use that value. Example Comp B's Jan 2022 is Non-Compliant but Feb'2022 is Compliant so we need to change Jan 2022's value to Compliant.

Can this be done in DAX or M for having a new column. I dont want a measure to be created.

ComputerName	Month	Status
Comp A	01-Nov-21	null
Comp A	01-Dec-21	null
Comp A	01-Jan-22	Compliant
Comp A	01-Feb-22	Compliant
Comp A	01-Mar-22	Non-Compliant
Comp B	01-Nov-21	Compliant
Comp B	01-Dec-21	null
Comp B	01-Jan-22	Non-Compliant
Comp B	01-Feb-22	Compliant
Comp B	01-Mar-22	Non-Compliant

[amitchandak]

@BI_Analyticz , a new column like

new column =
var _min = minx(filter(Table, [ComputerName] = earlier([ComputerName]) && [Month] >earlier([Month]) && not(isblank([Status])) ), [Month])
return
if(isblank([Status]) , maxx(filter(Table, [ComputerName] = earlier([ComputerName]) && [Month] =_min ), [Status]),[Status])

[BI_Analyticz]

@amitchandak Thanks for replying. Is there a way to pickup the value of Feb 2022 which is max month -1 or n-1

[v-kkf-msft · Accepted solution]

Hi @BI_Analyticz ,

Please try the following formula:

Column = 
VAR FirNonBlank =
    CALCULATE (
        MIN ( 'Table'[Month] ),
        FILTER (
            'Table',
            'Table'[Status] <> BLANK ()
                && 'Table'[ComputerName] = EARLIER ( 'Table'[ComputerName] )
        )
    )
RETURN
    SWITCH (
        [Status],
        "",
            CALCULATE (
                MAX ( 'Table'[Status] ),
                FILTER (
                    'Table',
                    'Table'[Month] = FirNonBlank
                        && 'Table'[ComputerName] = EARLIER ( 'Table'[ComputerName] )
                )
            ),
        "Non-Compliant",
            CALCULATE (
                MAX ( 'Table'[Status] ),
                FILTER (
                    'Table',
                    'Table'[ComputerName] = EARLIER ( 'Table'[ComputerName] )
                        && 'Table'[Month] = EDATE ( EARLIER ( [Month] ), 1 )
                )
            ),
        [Status]
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.