Calculate percentage difference between count of two dates

[Anonymous]

My data set :

Date	Category	Count
01-05-2020	ABC	313
01-05-2020	DEF	1000
02-05-2020	ABC	33
02-05-2020	DEF	33
03-05-2020	ABC	38
03-05-2020	DEF	21

My expected outcome is to get the rate values as shown below for respective dates:

Category	01-05-2020	02-05-2020	03-05-2020	01-05-2020(new)	02-05-2020(new)	03-05-2020(new)
ABC	313	33	38	N/A	-89%	15%
DEF	1000	166	21	N/A	-83%	-87%

How to write the following formula in DAX : (33 * 100 / 313) - 100 = -89% or ([COUNT OF TODAY'S DATE] * 100 / [COUNT OF YESTERDAY'S DATE]) - 100

[amitchandak]

@Anonymous , You can get the last date like this using date table

Last Day Non Continous = CALCULATE(sum('order'[Qty]),filter(all(Table),Table[Date] =MAXX(FILTER(all(Table),Table[Date]<max(Table[Date])),Table[Date])))
Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(Table[Date],-1,Day))

This Day =SUM(Sales[Sales Amount])

Then take a diff % like divide(([This Day]-[Day behind Sales]),[Day behind Sales])

[Anonymous]

Sorry, I'm very new to this.

Where should I apply this? Here?

@amitchandak 

[dax · Accepted solution]

Hi @Anonymous , 

You need to create measure in powerbi design pane instead of in power query pane. You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.