variance calculation

[MarcUrdang]

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

[nandukrishnavs]

@MarcUrdang 

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 🙂

[amitchandak]

@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)

[MarcUrdang]

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

[MarcUrdang]

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

[jdbuchanan71 · Accepted solution]

@MarcUrdang 

Give this a try.

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