topic Re: Need to get LASTNONBLANK value and the one prior to that, based on the date column in DAX Commands and Tips

Need to get LASTNONBLANK value and the one prior to that, based on the date column

julesdude — Tue, 06 Sep 2022 23:57:44 GMT

Hi all,

My problem is a relatively straightfoward one. I have a table that essentially looks like this:

Ref	Date	Value
1	07/09/2022
1	03/04/2020	2000
1	06/02/2021
1	09/02/2019	5000

For the value column, some data in rows is blank - however the row needs to be kept as other important info remains in columns not in my example here.

I want to display a table in my report where there are 4 columns:

The first is easy - I just drag the Ref field across to the visual

The second - to take the last value based on date - I already have this -

Current Value = lastnonblankvalue(Table[Date], max(Table[Value]))

However, the third column is where my struggle lies - I need to get the next blank value (using the date field to go back in time) to give me 5000.

My final column is fine - it calculates the % difference between the amounts:

% Diff =

VAR Delta = [Current Value] - [Previous Value]

RETURN

CALCULATE(DIVIDE(Delta, [Previous Value],blank()))

If anyone could help me with the third column I'd be grateful, thank you.

Re: Need to get LASTNONBLANK value and the one prior to that, based on the date column

amitchandak — Wed, 07 Sep 2022 02:29:59 GMT

julesdude , Not very clear

Create a rank on date

Rank= if(Isblank([Date]), Blank(), Rankx(filter(Table, [Ref] =earlier([Ref]) ), [Date],,asc, dense)

now measure

this ref date = calculate(Sum(Table[Value]), filter(allselected(Table), [Ref] =max([Ref]) && [Rank] =max([Rank]) ))

Last ref date = calculate(Sum(Table[Value]), filter(allselected(Table), [Ref] =max([Ref]) && [Rank] =max([Rank])-1 ))

Re: Need to get LASTNONBLANK value and the one prior to that, based on the date column

julesdude — Wed, 07 Sep 2022 08:10:31 GMT

Hi amitchandak

Sorry if I was unclear. Is your example still best to use based on the below-hope makes clearer!

Basically I have this table in my data model:

Ref	Date	Value
1	07/09/2022
1	03/04/2020	2000
1	06/02/2021
1	09/02/2019	5000
2	2018	1000
2	2022
2	2021

In my report I need it displayed like this:

Ref	CurrentValue	PreviousValue	% Diff
1	2000	5000	[% diff here]
2	1000		[no % difference if Current Value or previous value blank]

Logic : find last value and previous value going back in time using the date column, skipping any blanks. If there is only one value found, or if there is no value found, just leave % blank.