How do I calculate the difference between the newest value and the oldest value?

[lindelwebhebhe]

I have a dataset that has yyyymm datestamps, for example July 2020 is 202007 going all the way back to July 2019 ie. 201907.  In another column are values that are %.  I would like to calculate the difference between the newest % and the oldest percent divided by the newest %.  (Newest % - Oldest %) / Newest %.    The value fiield in which I would like to calculate is [Penetration of wealth into retail].   See screenshot of the table. I added a DateSort field as the cycle_num is not a date type.

[amitchandak]

@lindelwebhebhe , do need a row-wise difference or month wise. In case you need month-wise you can use time intelligence

I would prefer to create a date column or use the Rank way, example of the week shared

date =date(left([cycle_num],4),right([cycle_num],2),1)

Example with a date table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
Next month value =  CALCULATE(sum('table'[total hours value]),nextmonth('Date'[Date]))
previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[DataZoe]

@lindelwebhebhe i think I am following you, but correct me if I am wrong. I used these measures:

% Penetration of Wealth Into Retail = averagex(Wealth,Wealth[Penetration of Wealth Into Retail])

Oldest % = FIRSTNONBLANKVALUE(Wealth[cycle_num],[% Penetration of Wealth Into Retail])

Newest % = LASTNONBLANKVALUE(Wealth[cycle_num],[% Penetration of Wealth Into Retail])

Difference Between Oldest and Newest % = divide([Newest %]-[Oldest %],[Newest %])

You can change how the first number aggregates to your solution, average seemed to make most sense for me if I wanted to see different categories combined.

Edit: I had oldest and newest backwards, so fixed that!

[lindelwebhebhe]

I think this has solved my problem. Thank you very much 😊

[edhans]

can you provide data via the link directions below? The screenshot of data is good for expected results, which we also need, but not for data. I'm not typing that in.

Also, since you don't have full dates what happens if there is a tie for oldest and newest? Multiple 201907 for example for last year? How would we know which to select?

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum