How to create measure to find the difference % based last Fiscal Year rate

[PBI_newuser]

Hi, I want to create a measure "Rate" based on the below logic.

• Cost_LY = Sum Cost of last fiscal year (e.g FY20)
• Revenue_LY = Sum Revenue of last fiscal year (e.g. FY20)
• Rate for Oct-20 = (Revenue_LY/Cost_LY) / (Revenue in Oct-20/Cost in Oct-20) -1
• Rate for Nov-20 = (Revenue_LY/Cost_LY) / (Sum Revenue from Oct-20 to Nov-20/ Sum Cost from Oct-20 to Nov-21) -1
• Rate for Jul-21 = (Revenue_LY/Cost_LY) / (Sum Revenue from Oct-20 to Jul-21/ Sum Cost from Oct-20 to Jul-21) -1

How to create a measure to calculate the logic of "Rate" ?

Date	Month	Fiscal Year	Cost	Revenue	Rate
10/01/2019	Oct-19	FY20	$            68.89	$          826.73
11/01/2019	Nov-19	FY20	$            58.89	$          484.42
12/01/2019	Dec-19	FY20	$            59.11	$          241.43
01/01/2020	Jan-20	FY20	$            79.38	$          765.33
02/01/2020	Feb-20	FY20	$            26.65	$          792.36
03/01/2020	Mar-20	FY20	$            80.00	$          597.57
04/01/2020	Apr-20	FY20	$            33.00	$          923.93
05/01/2020	May-20	FY20	$            38.80	$          192.31
06/01/2020	Jun-20	FY20	$            34.97	$          452.41
07/01/2020	Jul-20	FY20	$            58.27	$          220.50
08/01/2020	Aug-20	FY20	$            21.18	$          581.39
09/01/2020	Sep-20	FY20	$            98.38	$          990.55
10/01/2020	Oct-20	FY21	$            61.08	$          405.61	-38%
11/01/2020	Nov-20	FY21	$            27.32	$          607.67	7%
12/01/2020	Dec-20	FY21	$            78.75	$          753.96	-2%
01/01/2021	Jan-21	FY21	$            65.01	$          753.49	1%
02/01/2021	Feb-21	FY21	$            13.13	$          108.61	0%
03/01/2021	Mar-21	FY21	$            46.29	$          168.36	-11%
04/01/2021	Apr-21	FY21	$             1.77	$          553.86	6%
05/01/2021	May-21	FY21	$            51.64	$          571.10	6%
06/01/2021	Jun-21	FY21	$            36.84	$          988.83	20%
07/01/2021	Jul-21	FY21	$             8.31	$          629.58	32%
08/01/2021	Aug-21	FY21
09/01/2021	Sep-21	FY21

[amitchandak · Accepted solution]

@PBI_newuser , do you need YTD based, Sep as year-end 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"09/30"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"09/30"))

Same month last year

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

[PBI_newuser]

Hi @amitchandak , thank you so much for the suggestions. But, I want to calculate the sum of full fiscal year for last year (from Oct-1 to Sep-30). How can I do that?

• Cost_LY = Sum Cost of last fiscal year (e.g FY20)
• Revenue_LY = Sum Revenue of last fiscal year (e.g. FY20)
• Rate for Oct-20 = (Revenue_LY/Cost_LY) / (Revenue in Oct-20/Cost in Oct-20) -1
• Rate for Nov-20 = (Revenue_LY/Cost_LY) / (Sum Revenue from Oct-20 to Nov-20/ Sum Cost from Oct-20 to Nov-21) -1