Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Give this a try.
Variance = DIVIDE ( [This YTD], [Last YTD] ) -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 🙂
@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 .. 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |