Calculating the difference and % difference between 2 columns

[Anonymous]

Hello, 

I am looking to calculate the numerical difference (in one column) and % difference (another column) between the last two dates for each row: 31st July and 17th August. The values in this table are from one measure, filtered by date and Financial Partner.

Thank you. 

[Anonymous]

Hello Amit, 

I have done the measures, many thanks. 

However, although the rank is correct - it is only calculating for previous day. How do I calculate it so that it is always max date, and then last day of the previous month i.e 31st July?

[amitchandak]

@Anonymous , Create a new table with distinct dates from this table

dates = distinct(Fin[date])

Then add new rank column

rank = rankx(dates, [Date],,asc,dense)

Join this with date of you date table

and create measure like

This Date= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Dates'),'Dates'[Rank]=max('Dates'[Rank])))
Last Date= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Dates'),'Dates'[Rank]=max('Dates'[Rank])-1))

Now you can have diff = [This Date] - [Last Date]

diff % = divide([This Date] - [Last Date], [last Date])