topic Re: Search for the last value in the column in DAX Commands and Tips

Search for the last value in the column

Anonymous — Fri, 19 Jan 2024 11:40:35 GMT

Hi,

I need a solution with DAX in PowerBI. It may be calculated in a measure or new column.

Search for the last value (not BLANK) in the "VALUE2" column and return it in the "NewField" column.

WEEK	STATUS	VALUE1	VALUE2	NewField
2023KW01	Status2		2500	2500
2023KW01	Status1	75		2500
2023KW03	Status1	120		2500
2023KW04	Status1	200		2500
2023KW05	Status1	230		2500
2023KW06	Status1	280		2500
2023KW06	Status2		2700	2700
2023KW07	Status1	410		2700
2023KW08	Status1	460		2700

Many Thanks

sicos

Re: Search for the last value in the column

bhanu_gautam — Fri, 19 Jan 2024 15:08:22 GMT

Anonymous , Please try this method

NewField =
CALCULATE(
LASTNONBLANK('YourTable'[VALUE2], 1),
FILTER(
ALL('YourTable'),
'YourTable'[WEEK] <= EARLIER('YourTable'[WEEK]) &&
NOT(ISBLANK('YourTable'[VALUE2]))
)
)

Accept as solution and give kudos if it helps

Re: Search for the last value in the column

Anonymous — Fri, 19 Jan 2024 15:21:38 GMT

Thanks for the solution. It only works if the value in the "VALUE" field is ascending. It should also work with ascending and descending values.

WEEK	STATUS	VALUE1	VALUE2	NewField
2023KW01	Status2		2500	2500
2023KW01	Status1	75		2500
2023KW03	Status1	120		2500
2023KW04	Status1	200		2500
2023KW04	Status1	200		2500
2023KW04	Status2		2600	2600
2023KW06	Status1	280		2600
2023KW06	Status2		2400	2400
2023KW07	Status1	410		2400
2023KW08	Status1	460		2400

Many Thanks

Re: Search for the last value in the column

bhanu_gautam — Fri, 19 Jan 2024 15:29:49 GMT

Try this one

NewField =
VAR CurrentWeek = 'YourTable'[WEEK]
VAR LastNonBlankValue =
CALCULATE(
LASTNONBLANK('YourTable'[VALUE2], 'YourTable'[VALUE2]),
NOT(ISBLANK('YourTable'[VALUE2])),
FILTER('YourTable', 'YourTable'[WEEK] = CurrentWeek)
)
RETURN
LastNonBlankValue