Current year previous year by product name

[dnavin]

Order Date	Product	QtySold	Price
01-01-2020	1	11	5
01-02-2020	1	50	2
01-03-2020	2	100	1
01-04-2020	2	300	1
01-05-2020	3	500	1
01-06-2020	3	600	1
01-07-2020	4	700	1
01-08-2020	4	50	25
01-09-2020	5	1	80
01-10-2020	5	10	15
01-11-2020	6	15	12
01-12-2020	6	18	9
01-01-2021	6	6	87

We have above data; We need to achive below calculation out of it

Current Year qty YTD by product

Previous Year  qty YTD by product

Current Year price by product

Previous Year price by product

Avg Price current yr by Product 

Avg price previous year by product

Prime impact =(Avg price current year by product -average price previous by product) * current year total qty by product

i made 2020 and 2021 data as sample, but we have muiple years like that, we should be able to calculated above by month,quarter and year.

any help apprericated.

we tried mutiple ways, sameperiod last year,ytd etc but prime impact is never accurate.

Regards,

Revan

[amitchandak]

@dnavin ,

Use time intelligence for that, using date table

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[dnavin]

Dax provided does not really work.[below data is sample, my order table might have mutiple values  for orderdate too]

if i dont use date table got below error