Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Calculating the difference and % difference between 2 columns

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...
Need Help
amitchandak's avatar
amitchandak
Super User
4 years ago

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])