Price effect calculation in BI

Join with date table using the date and then time intelligence

sales per Kg = divide(sum(Table1[Gross Sales]),SUM(Table1[Volume]))

	YTD sales per Kg = CALCULATE([sales per Kg],DATESYTD(('Date'[Date]),"12/31"))
	YTD Volume = CALCULATE(sum(Table1[Volume]),DATESYTD(('Date'[Date]),"12/31"))
	Last YTD sales per Kg = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

	Price Effect = divide(([YTD sales per Kg]-[Last YTD sales per Kg ]),[YTD Volume])
	

If you only have year. move that to year table or change the code below

sales per Kg = divide(sum(Table1[Gross Sales]),SUM(Table1[Volume]))

	YTD sales per Kg = 
	var _year =max(allselected(year),year[Year])
	return
	CALCULATE([sales per Kg], all(year),table[year]=_year)
	
	YTD Volume = 
	var _year =max(allselected(year),year[Year])
	return
	 CALCULATE(sum(Table1[Volume]), all(year),table[year]=_year)
	 
	Last YTD sales per Kg = 
	var _year =max(allselected(year),year[Year])-1
	return
	CALCULATE(SUM(Sales[Sales Amount]),all(year),table[year]=_year)

	Price Effect = divide(([YTD sales per Kg]-[Last YTD sales per Kg ]),[YTD Volume])

You can hard code year value if required in var _year

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin