Calucate % difference of date & date -1

[Anonymous]

I've a table like this, and want to get 1) difference vs date-1; 2)% vs date-1

Below somehow can caluate difference between 2 selected date, but what if i want date-1?

2 Day difference % =

var _max = MAXX('transformed', 'transformed'[Date])

var _min = MINX('transformed', 'transformed'[Date])

var newdate = calculate(sum('transformed'[Value]),filter(ALL('transformed'[Date]),[Date]=_max))

var olddate = calculate(sum('transformed'[Value]),filter(ALL('transformed'[Date]),[Date]=_min))

return

if(olddate = 0, 0, ROUND((newdate-olddate)/olddate*100, 0))

ID	Name	Date	Value	difference vs date-1	% vs date-1
1	A	Wednesday, July 1, 2020	10	10	0%
1	A	Thursday, July 2, 2020	20	10	100%
1	A	Friday, July 3, 2020	100	80	400%
2	B	Wednesday, July 1, 2020	10	10	0%
2	B	Thursday, July 2, 2020	1	-9	-90%
2	B	Friday, July 3, 2020	7	6	600%

[Fowmy]

@Anonymous 

It seems you want to calculate the VALUE and % difference from the previous day. But, I am not sure you still want to do it within each ID, please explain:
How did you calculate 10, -10, and - in the last three lines?

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS  to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel
 

[Anonymous]

Thanks for pointing it out.

apologize for the copy & paste mistake. I updated to below.

Take B as example:

-9 = 1-10

-1000% =(1-10)/10*100

ID	Name	Date	Value	difference vs date-1	% vs date-1
1	A	Wednesday, July 1, 2020	10	10	0%
1	A	Thursday, July 2, 2020	20	10	100%
1	A	Friday, July 3, 2020	100	80	400%
2	B	Wednesday, July 1, 2020	10	10	0%
2	B	Thursday, July 2, 2020	1	-9	-90
2	B	Friday, July 3, 2020	7	6	600%

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

Create 2 calculated columns as below:

difference vs date-1 = 
var _previousvalue=CALCULATE(MAX('Table'[Value]),FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[Date]=EARLIER('Table'[Date])-1))
Return
'Table'[Value]-_previousvalue

% vs date-1 = 
var _previousvalue=CALCULATE(MAX('Table'[Value]),FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[Date]=EARLIER('Table'[Date])-1))
Return
IF(_previousvalue=BLANK(),0,DIVIDE('Table'[difference vs date-1],_previousvalue))

And you will see:

For details,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Fowmy]

@Anonymous

Please check the attached file, it answers your question: https://1drv.ms/u/s!AmoScH5srsIYgYF-iRdl6MKD76W4WA?e=ASxIyx

 

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS  to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

[AllisonKennedy]

Try something similar to what you have done, but instead of _max and _min use a FILTER on ALL date table to filter for date < SELECTEDVALUE date to get _prev date.