DAX LASTNONBLANK Help!

[gnanasek]

Hi,

I have the following dataset:

Date Balance
Jan £100
Feb
Mar £50
Apr £70
May
Jun
Jul

I do not have a balance for Feb at the moment. What I would like to see is £100 (I.E. my last non blank value). For May I would like to see £70 because that is what was my balance in April, and then automatically I would like June to be £70 as this was the value in May etc.

Is this possible? Or something similar?

Thanks
Gnana

 

[akice]

You haven't provided enough information to answer. Model design? Do you have a separate calendar table (recommended)? Output pivot layout? LASTNONBLANK could be the right track, but without more info would be just guessing.

 

[gnanasek]

Date is coming from a separate date table called DealingDate, Balance is coming from a transactions table. These two tables are joined by a datekey. I am then bringing this into a pivot table in the layout as mentioned above. Please let me know if you need further information. Thanks for the reply

 

[akice]

This could be what you are looking for.

 Measure := LASTNONBLANK( [Balance], DATESYTD( DealingDate[datekey] ) )

 

[gnanasek]

Thanks for your reply.

I tried this and I get the error: A table of multiple values was supplied where a single value was expected. I am using Date instead of DateKey because DatesYTD is a date function and my datekey is a number.

Thanks

 

[akice]

Wow. Sorry about that. Not sure what I was thinking there as I totally messed that one up. What I should have put down was:

[COLOR=#333333]Measure := CALCULATE ( [Balance], LASTNONBLANK( DATESYTD( DealingDate[date] , [Balance] ) )[/COLOR]