Forum Discussion

aashok611's avatar
aashok611
Frequent Visitor
5 years ago
Solved

How to fill missing out row with below valid value, like previous row content

Hi All,

Please check below table, i took a portion of a big dateset, i need to fill above cell identified as "Check", with below cell value ,

for single above value with below DAX i can get, not more than one value i could not get, Hope some one can

if('F_Data (2)'[Custom] = "Check",
LOOKUPVALUE(
'F_Data (2)'[Custom],
'F_Data (2)'[Index],
'F_Data (2)'[Index] +1 ),
'F_Data (2)'[Custom])

 

DNoIndexProgressDate1Date2 (Check)Required like this
ABC-CC-DD-EE-01'115%10-Jan-202110-Jan-202110-Jan-2021
ABC-CC-DD-EE-01'245%null Check15-Mar-2021
ABC-CC-DD-EE-01'3100%15-Mar-202115-Mar-202115-Ma-2021
ABC-CC-DD-EE-02'410%5-May-20215-May-20215-May-2020
ABC-CC-DD-EE-02'525% null Check12-Jun-2020
ABC-CC-DD-EE-02'665%null Check12-Jun-2020
ABC-CC-DD-EE-02'790%12-Jun-202112-Jun-202112-Jun-2020
ABC-CC-DD-EE-02'8100%null  
ABC-CC-DD-EE-03'95%10-Jan-2021 10-Jan-202110-Jan-2021
ABC-CC-DD-EE-03'1015%nullCheck15-Jun-2020
ABC-CC-DD-EE-03'1125%nullCheck15-Jun-2020
ABC-CC-DD-EE-03'1145%15-Jun-202115-Jun-202115-Jun-2020
ABC-CC-DD-EE-03'1275%nullCheck20-Jun-2020
ABC-CC-DD-EE-03'1390%nullCheck20-Jun-2021
ABC-CC-DD-EE-03'14100%20-Jun-202120-Jun-202120-Jun-2021
Previous row value
  • MFelix's avatar
    MFelix
    5 years ago

    Hi aashok611 ,

     

    Try the following code for a calculated column:

     

     

    CheckDateFill = 
IF (
    'CheckDateTable'[Date2 (Check)] = "Check",
    SELECTCOLUMNS (
        TOPN (
            1,
            FILTER (
                ALL ( 'CheckDateTable' ),
                'CheckDateTable'[Index] > EARLIER ( 'CheckDateTable'[Index] )
                    && 'CheckDateTable'[Date2 (Check)] <> "Check"
            )
        ),
        "DateCheck", 'CheckDateTable'[Date1]
    ),
    'CheckDateTable'[Date1]
)

     

     

     

1 Reply

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi aashok611 ,

     

    Try the following code for a calculated column:

     

     

    CheckDateFill = 
IF (
    'CheckDateTable'[Date2 (Check)] = "Check",
    SELECTCOLUMNS (
        TOPN (
            1,
            FILTER (
                ALL ( 'CheckDateTable' ),
                'CheckDateTable'[Index] > EARLIER ( 'CheckDateTable'[Index] )
                    && 'CheckDateTable'[Date2 (Check)] <> "Check"
            )
        ),
        "DateCheck", 'CheckDateTable'[Date1]
    ),
    'CheckDateTable'[Date1]
)