Forum Discussion
Variance Between Years
Any help?
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
Hi,
It is example of output, Another Screen shot below and a small data set. Hope this helpSample DataEnd output
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?
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
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!
