Return the value for the next minimum date if the value field is empty, and group by ID.

[denengland]

Hi,

In dax, I would like to return the value for the next minimum date if the value field is empty, which should be group by ID. Also, need to return the coresponding value in the associated category colum.

I have only one table and have attempted to use several measures, the example measure below does not work well when there are null values. Please help!

CALCULATE ( MIN ( Query1[value]), FIRSTDATE ( Query1[date]) )

[denengland]

Thanks, I was able to fill in 50 for ID1 for the 08/13/2020 date. However, this is not the solution i'm looking for. I was hoping that a dax measure could be written to return the other columns as well. So for ID 1, I would need one row for ID1 with the earliest date (i.e.,07/13/2021), which has a value (i.e.,50), and the category (A) associated with that row .

[eliasayyy]

hello @denengland  can you please explain what you mean by next minimum date maybe show us an expected result in excel

[denengland]

Thank you for responding!

In my initial post there is picture which showed an ID with three dates, the first date is missing a value. I would like a dax measure written which will pull in the non-missing value for the next earliest date.

[eliasayyy]

so if i understood correctly where id =1 you have missing 8/13/2020 missing so you need to fill it as 50 which is the enxt date?

[denengland]

yes, that's correct

[eliasayyy]

Please refer to the solution below of it helped consider accepting it as a solution 

[eliasayyy]

if it is the case then go to power query , i created a sample of what you made

group by id and choose the all rows and name it Table

add a new custom column 

let
    NewTable = [Table],
    SortedTable = Table.Sort(NewTable, {"Date"}),
    IndexedTable = Table.AddIndexColumn(SortedTable, "Index", 0, 1),
    ShiftedValue = Table.AddColumn(IndexedTable, "Next Value", each 
        try IndexedTable[Value]{[Index] + 1} 
        otherwise null),
    ReplacedValue = Table.AddColumn(ShiftedValue, "Next Date Value", each 
        if [Value] = null then [Next Value] else [Value]),
    FinalTable = Table.RemoveColumns(ReplacedValue, {"Index", "Next Value"}) // Remove unnecessary columns
in 
    FinalTable

after that expand the new column 

results

if its waht you want please accept as solution if no then please explain more