cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Post Partisan

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

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.

1 ACCEPTED SOLUTION
Resolver I

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

5 REPLIES 5
Resolver I

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))

Post Partisan

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?

Resolver I

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

Post Partisan

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

Resolver I

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))

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Top Solution Authors
Top Kudoed Authors