Forum Discussion
Helper IYoY comparison for specific months or Quarters
Hi Folks,
Every month we save the sales deals that were closed and remaining pipeline in an Excel file. How do I compare the report in June21 to that of June22 but for the next 6 months from the date of the report? Or even the following 2 Quarters?
e.g. Team1 Sales for the next 6 months as at Jun-21 was 5,095,963 and in Jun-22 it was 4,571,858
| For the next 6 months | ||
| Sales Team | Jun-21 | Jun-22 |
| Team 1 | 5,095,963 | 4,571,858 |
| Team 2 | 5,429,086 | 7,730,519 |
| Team 3 | 3,776,149 | 3,489,192 |
| Team 4 | 2,928,988 | 3,361,777 |
It's really vital that the user can choose how many months or Quarters they would would like to compare.
Pablo_B , you can use use trailing year measure
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))but if you want to display 6-months of data from the selected date then you need an independent date table
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -6) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))new measure last year =
var _max1= maxx(allselected(Date1),Date1[Date])var _max = date(year(_max1)-1, month(_max1), day(max1))
var _min = eomonth(_max, -6) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))
3 Replies
- amitchandak
Super User
Pablo_B , you can use use trailing year measure
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))but if you want to display 6-months of data from the selected date then you need an independent date table
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -6) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))new measure last year =
var _max1= maxx(allselected(Date1),Date1[Date])var _max = date(year(_max1)-1, month(_max1), day(max1))
var _min = eomonth(_max, -6) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))
Pablo_BThank you for coming back to me so quickly.
2 questions:
1. how do I make it dynamic so that the user can choose if it's 6 months or 3 months? maybe through a slicer
2. How do I do the same thing for Quarters? e.g. They choose Q3 & Q4 of that same year as the report
Thanks again- Pablo_B
Could you please tell me what I'm doing wrong here as last year's figure is going in the wrong date?