cancel
Showing results 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

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Any help?

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

@ 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!:
Power BI Cookbook Third Edition (Color)

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

Super User

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

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
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.

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%

Super User

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

@ 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!:
Power BI Cookbook Third Edition (Color)

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)

Super User

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?

@ 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!:
Power BI Cookbook Third Edition (Color)

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors