Variance Between Years

[Anonymous]

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 below each KPI(Would be pretty simple in excel). Could you please help me to find a way out and if i could make this dynamic for all the tables would be great so that i do not have to create measure every time

[Greg_Deckler]

Is that an example of source data or is that an example of how you have data in a table visualization (output data). If that is output data, please provide example of source data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Hi,

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

[Ashish_Mathur]

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.

[Anonymous]

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!

[Ashish_Mathur]

Hi,

I do not understand your first question.  As regards the second one, the answr is yes.

[Anonymous]

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)

[Anonymous · Accepted solution]

Any help?

[Anonymous]

I have marked as solved by mistake. I am still looking for the way out

@Greg_DecklerCan anyone please help me out here, i am almost there. I have everything i need for the dashboard. ALl i need is to find the way to calcualte vairiance and variance%

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Anonymous]

@Greg_Deckler The solution did not work as i have to create 3 measures for each KPI(30 in total) and for 3 timeperiod(Current Month, YTD and FY)

Could you please suggest something optimal that i can use for all the KPI tables(Like in excel it would be 2018 Value -2017 Value)

[Greg_Deckler]

OK, a couple last questions, you already have YTD 2017 and YTD 2018 measures, correct? Also, is there any reason you cannot just UNION or Append all 6 or 7 tables together?

[Anonymous]

Thanks for your reply. I cannot use union or append as i am aiming to have this output tables displayed in the interface(like a tree) with filters for users to get desired output by selecting Customers, brands etc. Another reason is i have Month and FY too similar to YTD that i created using switch function(table stays as is, only the value change upon selection of YTD, Month and FY)

Is not there any way where we can just simply substract value of 2018-2017 of volume and sales(for variance abs) and Variance abs/2017(for variance%) value in the same table?

Final view wpuld be like in the link, query i posted a month ago https://community.powerbi.com/t5/Desktop/Produce-Table-hierarchy-from-raw-data/m-p/532661