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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
MarcUrdang
Post Patron
Post Patron

Variance and variance %

Please advise on how to create a variance and a variance % as per below:

I have created two calculations which compute a measure for a period of time. This year to date and last year to date:

 

1. This YTD:   CALCULATE(SUM('Table1'[measures]),DATESYTD((Date'])))

2. Last YTD:   CALCULATE(SUM('Table1'[measures]),DATEADD(FILTER(DATESYTD('Table1'[Date]),'Table1'[Date]<TODAY()),-1,YEAR))

 

I have a table whih show various cities and the above two calcs show the values per city.

For example if CityA had 10 for last YTD and 15 for this YTD then the Variance must show 5 and the % difference would be 50%

 

Thank you

Marc

1 ACCEPTED SOLUTION
v-gizhi-msft
Community Support
Community Support

Hi,

 

See my attached pbix file.

Hope this can help.

 

Best Regards,

Giotto

View solution in original post

8 REPLIES 8
v-gizhi-msft
Community Support
Community Support

Hi,

 

See my attached pbix file.

Hope this can help.

 

Best Regards,

Giotto

Greg_Deckler
Super User
Super User

Variance = [This YTD] - [Last YTD]

Variance% = ([This YTD] - [Last YTD]) / [Last YTD]

 



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

DAX is easy, CALCULATE makes DAX hard...

Hi Greg .. I see you already posted back on this ... I originally tried this .... but get the same number for each as the example shows below:

 

hank you so much for the reply .. on the variance amount (this year - last year) .. the value doesn't reflect correctly. The variance amount is the same as the actual amount.. it shows as follows:

 

Country        2019     var          2020    var

Spain            100      100          150     150

 

it shoud show

 

Country        2019     var          2020    var

Spain            100      0             150      50

Well, those are your measures @MarcUrdang ! 🙂

 

It is difficult to say, but you are using time "intelligence" so that's a crap shoot in my opinion. You are using them a little weird, you might try SAMEPERIODLASTYEAR. I assume you have a separate Date table.

 

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

 

If all that fails sample 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

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.



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

DAX is easy, CALCULATE makes DAX hard...

I was originally using the itelligence option (quick measures) which actually worked .. but changed the calcs manually shown in my original post ... it might be 'weird' but as a fairly new user I'm just trying to find my way.

 

Will try your idea and await other replies which might also help show me the way.

 

thanks for your help thus far

Marc

@MarcUrdang - Just remember, sample data just has to represent your actual data enough to recreate the problem.



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

DAX is easy, CALCULATE makes DAX hard...
sevenhills
Super User
Super User

Variance = [This YTD] - [Last YTD]

 

Variance % = DIVIDE ( ([This YTD] - [Last YTD]), [Last YTD], 0)

 

- SV

Thank you so much for the reply .. on the variance amount (this year - last year) .. the value doesn't reflect correctly. The variance amount is the same as the actual amount.. it shows as follows:

 

Country        2019     var          2020    var

Spain            100      100          150     150

 

it shoud show

 

Country        2019     var          2020    var

Spain            100      0             150      50

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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