cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dnavin
New Member

Current year previous year by product name

Order DateProduct  QtySoldPrice
01-01-20201115
01-02-20201502
01-03-202021001
01-04-202023001
01-05-202035001
01-06-202036001
01-07-202047001
01-08-202045025
01-09-20205180
01-10-202051015
01-11-202061512
01-12-20206189
01-01-20216687

 

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

2 REPLIES 2
amitchandak
Super User IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

datafile.PNG

 

datetable.PNGfinaloutput.PNGsameperiodlastyear.PNG

 

if i dont use date table got below error

sameperiodIssue.PNG

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors