Calculate monthly difference from the latest date with value

[Anonymous]

Hello, 

I'm looking to write a measure that would calculate the difference between the latest price of an item and its price in the previous month. My dataset has different latest dates for each item, so I was using LASTDATE and DATESBETWEEN to get the latest price. However, I am not sure how to get the previous month's price. 

Latest Price = 
VAR _LastDate = LASTDATE(Products[Date])
RETURN

CALCULATE(SUM(Products[Price]), DATESBETWEEN(Calendar[Date], _LastDate, _LastDate))

In the example below, the Latest Price measure returns $4 for Bananas, and $7 for Apples. 

The price difference I am looking for would be:

• Bananas: 4-6 = -2 (March - Feb)
• Apples: 7-4 = 3 (April - March). 

Item	Date	Price
Bananas	Jan 2020	5
Bananas	Feb 2020	6
Bananas	March 2020	4
Apples	Jan 2020	2
Apples	Feb 2020	5
Apples	March 2020	4
Apples	April 2020	7

I also need the percentage difference but that should be straightforward once I figure this out. Thank you in advance!

Best, 

Alex

[Anonymous]

Thank you everyone for your help! 😊

[mahoneypat · Accepted solution]

Please try these expressions:

Latest Price =
VAR __latestdate =
MAX ( 'Table'[Date] )
RETURN
CALCULATE ( SUM ( 'Table'[Price] ), 'Table'[Date] = __latestdate )

Prev Price =
VAR __latestdate =
MAX ( 'Table'[Date] )
VAR __prevdate =
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] < __latestdate )
)
RETURN
CALCULATE ( SUM ( 'Table'[Price] ), 'Table'[Date] = __prevdate )

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[aj1973]

@mahoneypat 

What if you had a row with April 2 in it! this formula VAR __latestdate doen't not show the last date of the previous month, it shows the row of April 1.

That's why i want to ask @Anonymous if in his Table there is only one row for evry 1st of the month or many dates and if he is using a Calendar date in order to use Time Intelligent DAX formulas accuratly!

Thanks

[mahoneypat]

You are correct about an April 2 values.  From the example data that seemed to not be the case, but it would be easy to adapt that approach use current and prev month if needed.

Regards,

Pat

[TomMartens]

Hey @Anonymous ,

I assume you have a measure that returns the last price (the price of the latest date), maybe this measure looks like this:

last Amount = 
LASTNONBLANKVALUE('Calendar'[Date] , [Amount (explicit measure)]) 

To find the latest price for the previous month you can restrict the avaiable days to the previous months like so:

last amount previous month = 
var maxDate = CALCULATE(MAX('Calendar'[Date]))
var tDatesPreviousMonth = 
	DATESBETWEEN(
		'Calendar'[Date] 
		, EOMONTH(maxDate , -2) + 1 
		, EOMONTH(maxDate , -1)
		)
return
CALCULATE(
	[last Amount]
	, tDatesPreviousMonth 
	)

Hopefully, this provides some new ideas on how to tackle your challenge.

Regards,

Tom