Forum Discussion
Variance Between Years
Any help?
Hi,
Since you only have Year (Period column) with no date, we will have to first create a Date columm in the Data model by using the following calculated column formula =DATE([Period],12,1). Then create a Calendar Table and build a relationship from the Date column (created with the calculated column formula above) to the Date column of the Calendar Table. In the Calendar Table, create a Year column by using =YEAR(Calendar[Date]). In your visual, drag Year from the Calendar Table. Write these measures:
Volume=SUM(Data[Vol])
Volume in PY=CALCULATE([Volume],PREVIOUSYEAR(Calendar[Date]))
Absolute volume change=[Volume in PY]-[Volume]
% volume change=[Absolute volume change]/[Volume]
Hope this helps.
Thanks for your reply. I think i am almost there
Two questions -1. I have Month, FY and YTD view for all the KPIs(altogether 7-8 in form of table), will the solution above handle this?
2. I have around 7-8 KPIs(volume, sales, promotion, index etc). So do i have to calculate Abs variance and Variance% for all of them?
Thanks!
Hi,
I do not understand your first question. As regards the second one, the answr is yes.
Regarding question 1, attached is the view. you will find 3 views YTD,FY and Month. So my question was like for CY and PY, do i have to calculate separate measure for FY and Month. I am getting the results i need, all i am looking for is to find a way to calculate the Difference between Value of 2018 and 2017 in the matrix table.
Reagarding 2nd Question, is not there any easy way. Because i am already creating around 24 dax query for 7 KPIs and for 3 different views(FY,YTD and Month)
Any help?