topic Lastnonblank not returning correct results in DAX Commands and Tips

Lastnonblank not returning correct results

Anonymous — Wed, 28 Sep 2022 05:58:55 GMT

I'm trying to return the last non-blank row of the "Target stage outcome" column based on the "Entity" column. However, using the lastnonblank dax formula is not yielding the correct results.

I have attached the example fact table below.

Here is the results that I'm trying to get are those in the red box:

I've created a summary table to see the results

Despite the "Index" and "Last day" returning the correct results, the "Stage" field is not returning the correct result.

This is the "Target" table that I'm using in my summary table above

I would appreciate if someone could help me here.

Thanks

Re: Lastnonblank not returning correct results

wdx223_Daniel — Wed, 28 Sep 2022 06:57:33 GMT

LASTNONBLANK, sorts the column in the first parameter, then get the last value of which the second parameter is non blank

Re: Lastnonblank not returning correct results

Anonymous — Wed, 28 Sep 2022 07:47:12 GMT

wdx223_Daniel Would that mean that it would sort the "target stage outcome" first then only determine the lastnonblank for the "Response date"?

Re: Lastnonblank not returning correct results

wdx223_Daniel — Wed, 28 Sep 2022 08:06:59 GMT

try LASTNONBLANKVALUE(Target[Reponse Date],MAX(Target[Target Stage Outcome]))

Re: Lastnonblank not returning correct results

Anonymous — Wed, 28 Sep 2022 08:31:21 GMT

Thanks wdx223_Daniel. It worked but should there be the same date, then it doesn't work as intended. How should I deal with it if I dont use the "Response date" field?

Re: Lastnonblank not returning correct results

wdx223_Daniel — Wed, 28 Sep 2022 08:34:41 GMT

how about using the Target[Index] as the first parameter?