Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
julesdude
Post Partisan
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)

 

AssetDateValue
aaa24/07/202020
aaa  
aaa25/07/202230
aaa01/04/201950
aaa04/06/201940
bbb17/03/202170
bbb10/02/201950
bbb  
bbb14/03/202040
bbb11/02/201880
bbb10/02/201790
ccc02/02/201930
ccc02/02/201830

 

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

 

AssetCurrent ValuePrevious Value% Difference
aaa3020 
bbb7040 
ccc3030 

 

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

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

View solution in original post

5 REPLIES 5
arichard19
Resolver I
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))

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?

 

 

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

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

 

 

arichard19
Resolver I
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))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors