Forum Discussion
Percentage Change
Hello - My data is (# Incident Reports Received), (Fiscal Year when the Report was submitted) and (Region) I have created this matrix and looking to calculate the percentage change between the two years.
I am also trying to move the Fiscal Year as a header similar to the image below. Thoughts?
Anonymous , You can try measure like with 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")) This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year]))) Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1)) diff = [This Year]-[Last Year ] diff % = divide([This Year]-[Last Year ],[Last Year ])
6 Replies
- amitchandak
Super User
Anonymous , You can try measure like with 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")) This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year]))) Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1)) diff = [This Year]-[Last Year ] diff % = divide([This Year]-[Last Year ],[Last Year ])amitchandak Thank you. This is hard for me to comprehend. Our fiscal year runs Sept 1 - Aug 31. Do I use 09/31 instead of the 12/31?