Forum Discussion
Simple Variance Analysis
Hello all,
Hoping for some help as I am new to Power BI. I am being encouraged to use this at work for some simple financial analysis, but am struggling to do some standard variances, which i used to do easily in excel. Example:
| Product Number | July | Aug | Variance | % Variance |
| 123 | 100 | 120 | 20 | 20% |
| 456 | 2500 | 4000 | 1500 | 60% |
How do I create the variance and % variance columns in PowerBI? (Apologies if this is a basic question and asked many times before!)
thanks in advance
rocky84 , You have create measure for this month and last month and use those
examples measures
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))Variance
diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
4 Replies
- Ashish_Mathur
Super User
Hi,
The difference can be negative. Share some data to work with and show the problem clearly.
When calculating variance, the difference is provided as a positive number. I have a data set where I am attempting to calculate Y1 planned - Y1 actual, and it should generate negative numbers in some cases. Can you explain why Power BI does not allow the difference to be negative numbers?
PBIdashboardsPost Patron
The DAX approach in the accepted answer works well. Worth adding for anyone coming from Excel who finds this workflow: the measure-per-metric pattern scales up quickly once you add more months or more products, you end up maintaining a lot of measures.
For this exact use case (July vs Aug, Variance, % Variance across products), Flexa Tables on AppSource lets end-users add the variance column directly in the published report select the two periods, the absolute and % variance appear automatically. No DAX measures to write or maintain.
Free trial on AppSource
- amitchandak
rocky84 , You have create measure for this month and last month and use those
examples measures
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))Variance
diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
