Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Variance Between Years

Hi,   I have 6-7 tables with different KPIs (Sales, distribution etc for year 2017 and 2018 calculated at Month,YTD and QTD. Example below   All i need is to show the variance between the years b...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Any help?

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi,

 

It is example of output, Another Screen shot below and a small data set. Hope this helpSample DataEnd output

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

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.