Forum Discussion

Pablo_B's avatar
Pablo_B
Icon for Helper I rankHelper I
4 years ago
Solved

YoY 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 da...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

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

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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_B's avatar
    Pablo_B
    Icon for Helper I rankHelper I
    4 years ago

    Thank 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's avatar
    Pablo_B
    Icon for Helper I rankHelper I
    4 years ago

    Could you please tell me what I'm doing wrong here as last year's figure is going in the wrong date?