Forum Discussion
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. 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
5 Replies
MarcUrdang , try like
YTD QTY forced= var _max = today() return calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max) //calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max)) LYTD QTY forced= var _max = date(year(today())-1,month(today()),day(today())) return CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max) //TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)Hi Amit .. thanks for trying to help. hope all well your side
So to clarify:
1. My calcs work fine for indiviudal fields
2. To get the difference btw the two I use yours ... is it once calc? Also why the // this is to make what follows just text?
Sorry that i'm a little confused .. can you help clarify?
tx
Marc
Hi Amit .. got it to work .. .WOW WOW ... you are a legend!!!!! I get scared to think of does one ever get to be as knowledgeable as someone like you.
thank you soooo very much
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 🙂
