Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 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

 

variance.JPG

1 ACCEPTED SOLUTION
Anonymous
Not applicable

12 REPLIES 12
Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi,

 

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

View.JPG

 

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
Not applicable

Any help?

Anonymous
Not applicable

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%

 

 

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...


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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)

 

 

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?


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors