topic Re: Return the value for the next minimum date if the value field is empty, and group by ID. in DAX Commands and Tips

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

denengland — Tue, 01 Aug 2023 14:05:37 GMT

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]) )

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

eliasayyy — Tue, 01 Aug 2023 14:22:38 GMT

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

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

denengland — Tue, 01 Aug 2023 14:40:54 GMT

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.

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

eliasayyy — Tue, 01 Aug 2023 14:53:55 GMT

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?

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

eliasayyy — Tue, 01 Aug 2023 15:09:58 GMT

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

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

denengland — Tue, 01 Aug 2023 15:12:54 GMT

yes, that's correct

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

eliasayyy — Tue, 01 Aug 2023 17:45:12 GMT

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

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

denengland — Wed, 02 Aug 2023 19:27:39 GMT

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 .