How to get the date of corresponding last nonblank value

[PunchBird]

Hi,

I have a dataset for which for some dates, the latest values are missing, for example:

date         value1 value2
1/1/2021      3       4
2/1/2021      2       2
3/1/2021      3       6
4/1/2021               7

Now I need to get the dates belonging to each last nonblank value. So to be clear: I don't want the value but the date.

So I need:

LastDateValue1 = 3/1/2021

LastDateValue2 = 4/1/2021

I tried creating a DAX measure for this with but I can't get it to work. Could anyone please help? Thanks so much!

[Anonymous]

Hi  @PunchBird ,

Here are the steps you can follow：

1. Create measure.

LastDateValue1 =
IF(MAX('Table'[Value1])=BLANK(),
MAXX(FILTER(ALL('Table'),'Table'[Value1]<>BLANK()&&'Table'[date]<=MAX('Table'[date])),[date]),
MAX('Table'[date]))

LastDateValue2 =
IF(MAX('Table'[Value2])=BLANK(),
MAXX(FILTER(ALL('Table'),'Table'[Value2]<>BLANK()&&'Table'[date]<=MAX('Table'[date])),[date]),
MAX('Table'[date]))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[PunchBird]

It's weird because when I test this I still get 4/1/2021 for value1. The solution also looks a bit cumbersome, compared to what @amitchandak proposed above. But thanks anyway for your help!

[PaulDBrown]

Try:

LastDateValue1 =
MAXX (
    FILTER ( Table, NOT ( ISBLANK ( SUM ( Table[Value1] ) ) ) ),
    Table[Date]
)

and

LastDateValue2 =
MAXX (
    FILTER ( Table, NOT ( ISBLANK ( SUM ( Table[Value2] ) ) ) ),
    Table[Date]
)

[PunchBird]

Hi, thanks! I tested this one but it didn't work... I get 4/1/2021 for both values, which isn't correct 

[amitchandak · Accepted solution]

@PunchBird , Create two measures like

maxx(filter(Table, not(isblank(Table[Value1]))),Table[Date])

maxx(filter(Table, not(isblank(Table[Value2]))),Table[Date])

[PunchBird]

@Anonymous thanks, this worked, exactly what I needed!