Forum Discussion
Solved
variance calculation
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....
Sample dataset
| Date | City | Sales |
| 01-01-2019 | CityA | 200 |
| 01-02-2019 | CityA | 250 |
| 01-03-2019 | CityA | 300 |
| 01-04-2019 | CityA | 350 |
| 01-05-2019 | CityA | 400 |
| 01-06-2019 | CityA | 450 |
| 01-07-2019 | CityA | 300 |
| 01-08-2019 | CityA | 150 |
| 01-09-2019 | CityA | 175 |
| 01-10-2019 | CityA | 200 |
| 01-11-2019 | CityA | 225 |
| 01-12-2019 | CityA | 250 |
| 01-01-2020 | CityA | 275 |
| 01-02-2020 | CityA | 300 |
| 01-03-2020 | CityA | 325 |
| 01-04-2020 | CityA | 350 |
| 01-05-2020 | CityA | 375 |
| 01-01-2019 | CityB | 150 |
| 01-02-2019 | CityB | 155 |
| 01-03-2019 | CityB | 160 |
| 01-04-2019 | CityB | 165 |
| 01-05-2019 | CityB | 170 |
| 01-06-2019 | CityB | 175 |
| 01-07-2019 | CityB | 180 |
| 01-08-2019 | CityB | 185 |
| 01-09-2019 | CityB | 190 |
| 01-10-2019 | CityB | 195 |
| 01-11-2019 | CityB | 200 |
| 01-12-2019 | CityB | 205 |
| 01-01-2020 | CityB | 210 |
| 01-02-2020 | CityB | 215 |
| 01-03-2020 | CityB | 220 |
| 01-04-2020 | CityB | 225 |
| 01-05-2020 | CityB | 230 |
YTD Sales = CALCULATE(SUM('Table'[Sales]),DATESYTD('Table'[Date].[Date]))Prv YTD Sales = CALCULATE(SUM('Table'[Sales]),DATESYTD(SAMEPERIODLASTYEAR('Table'[Date].[Date])))YTD difference = [YTD Sales]-[Prv YTD Sales]Variance % = DIVIDE([YTD difference],[Prv YTD Sales],BLANK())
If you are not using Date Hierarchy, you can remove .[Date] from the above measures.
YTD Sales = CALCULATE(SUM('Table'[Sales]),DATESYTD('Table'[Date]))Prv YTD Sales = CALCULATE(SUM('Table'[Sales]),DATESYTD(SAMEPERIODLASTYEAR('Table'[Date]])))
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂