topic Re: Return Value based on Last Date and Return Value Based on Second to Last Date (Penultimate Date) in DAX Commands and Tips

Return Value based on Last Date and Return Value Based on Second to Last Date (Penultimate Date)

julesdude — Mon, 25 Jul 2022 09:13:01 GMT

Hi there,

This might be quite a simple one - sorry that I am new to DAX and learning. I have the following table (simplified)

Asset	Date	Value
aaa	24/07/2020	20
aaa
aaa	25/07/2022	30
aaa	01/04/2019	50
aaa	04/06/2019	40
bbb	17/03/2021	70
bbb	10/02/2019	50
bbb
bbb	14/03/2020	40
bbb	11/02/2018	80
bbb	10/02/2017	90
ccc	02/02/2019	30
ccc	02/02/2018	30

I am trying to create a table in my report that will reference this table to make it look like this:

Asset	Current Value	Previous Value	% Difference
aaa	30	20
bbb	70	40
ccc	30	30

Basically I would like the asset listed 'distinctly', and then for current value column:

- each decided by finding the most recent date, and returning the value

For previous value

- find the second to last most recent date and return the value

For % difference

- The % difference between the above two

Any help really appreciated.

Re: Return Value based on Last Date and Return Value Based on Second to Last Date (Penultimate Date)

arichard19 — Mon, 25 Jul 2022 09:23:22 GMT

Try the following ->

LastDate = Calculate(SUM(Table1[Value]), LASTDATE(Table1[Date]))

SecondLastDate =
VAR LastDate = LASTDATE(Table1[Date]))
RETURN
Calculate(SUM(Table1[Value]), MAX(Table1[Date]) < LastDate))

Delta % =
VAR Delta = LastDate - SecondLastDate
Return
Calculate(Divide(Delta, SecondLastDate))

Re: Return Value based on Last Date and Return Value Based on Second to Last Date (Penultimate Date)

arichard19 — Mon, 25 Jul 2022 09:34:01 GMT

Edit to Above Formula ->

Try the following ->

ValueLastDate = Calculate(SUM(Table1[Value]), LASTDATE(Table1[Date]))

ValueSecondLastDate =

VAR SecondLastDate = CALCULATE(MAX(Table1[Date]), Table1[Date] < MAX(Table1[Date]))

VAR ValueSecondLastDate = Calculate(Table1[Value], Table1[Date] = SecondLastDate)

RETURN
ValueSecondLastDate

Value Delta % =
VAR Delta = ValueLastDate - ValueSecondLastDatee
Return
Calculate(Divide(Delta, ValueSecondLastDate))

Re: Return Value based on Last Date and Return Value Based on Second to Last Date (Penultimate Date)

julesdude — Mon, 25 Jul 2022 10:08:17 GMT

Hi arichard19

Many thanks for this! I have split into two measures. Measure 1:

ValueLastDate = Calculate(SUM(Table1[Value]), LASTDATE(Table1[Date]))

This works fine.

But measure 2:

ValueSecondLastDate =

VAR SecondLastDate = CALCULATE(MAX(Table1[Date]), Table1[Date] < MAX(Table1[Date]))

VAR ValueSecondLastDate = Calculate(Table1[Value], Table1[Date] = SecondLastDate)

RETURN
ValueSecondLastDate

This is giving me an error - Cannot find table 'Table1[Value]'. And the intellisense when typing only provides other DAX measures instead of any table and column reference.

What am I doing wrong?

Re: Return Value based on Last Date and Return Value Based on Second to Last Date (Penultimate Date)

arichard19 — Mon, 25 Jul 2022 10:12:14 GMT

It seems I forgot to include the SUM() function here.

Try ->
VAR ValueSecondLastDate = Calculate(SUM(Table1[Value]), Table1[Date] = SecondLastDate)

When writing measures, you need to use functions on the columns

Re: Return Value based on Last Date and Return Value Based on Second to Last Date (Penultimate Date)

julesdude — Mon, 25 Jul 2022 12:08:05 GMT

Thank you so much arichard19 for you help. Works perfectly. I just added the square brackets for the references to other measures where needed in the DAX