Forum Discussion
Solved
Calculation on last value date,
SO I have data in the following format Date Ticker Price 2020-11-30 X 20 2020-11-30 Y 5 2020-11-30 Z 2 2020-11-30 A 100 2020-10-30 X 10 2020-10-30 Y 15 2020-10-...
Good morning!
Here's a couple measures...
Current Price:=AVERAGE(Tickers[Price]) Previous Price:=CALCULATE( [Current Price], PREVIOUSMONTH('Calendar'[Date]) ) Price MTM Change:=[Current Price] - [Previous Price] Price MTM % Change:=DIVIDE( [Price MTM Change], [Previous Price], BLANK() )Using AVERAGE() because I'm not sure if you'll have more than one price in any given month. If you do, you might want to change that to MIN(), MAX() or whatever suits your needs.
Results
Good morning!
Here's a couple measures...
Current Price:=AVERAGE(Tickers[Price])
Previous Price:=CALCULATE(
[Current Price],
PREVIOUSMONTH('Calendar'[Date])
)
Price MTM Change:=[Current Price] - [Previous Price]
Price MTM % Change:=DIVIDE(
[Price MTM Change],
[Previous Price],
BLANK()
)Using AVERAGE() because I'm not sure if you'll have more than one price in any given month. If you do, you might want to change that to MIN(), MAX() or whatever suits your needs.
Results
Forgot to mention...you'll have to have a date table because of the time intelligence function for this to work correctly.