Forum Discussion
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
Hi,
See my attached pbix file.
Hope this can help.
Best Regards,
Giotto
8 Replies
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
Variance = [This YTD] - [Last YTD]
Variance% = ([This YTD] - [Last YTD]) / [Last YTD]
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-TITHW/m-p/434008
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.
Hi,
See my attached pbix file.
Hope this can help.
Best Regards,
Giotto
