Forum Discussion

Jitmondo's avatar
Jitmondo
Helper III
4 years ago
Solved

Getting 2 years back total !

Hi All,

 

something simple I think but can't get it to work.

 

I have a graph where I am showing last years won deals :

Last Year Closed Won = TOTALYTD([Closed Won Weighted Pipeline],SAMEPERIODLASTYEAR(Dates[Date]))
this works fine and is shown as a line.
 
my current measurs and visuals are dynamic and change with year selector slicer so this new measure would need to be too.
 
Is there a way to write a measure that shows the year before that ?
 
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Jitmondo ,

     

    You could try use edate() function. Please check this measure.

    measure =

    var _date =selectedvalue('date'[date])

    return
    calculate(sum([value]),filter(allselected('table'),'table'[date]>=date(year(_date)-2,1,1)&&'table'[date]<=edate(_date,-24)))

    Best Regards,

    Jay

4 Replies

  • Jitmondo's avatar
    Jitmondo
    Helper III
    4 years ago

    thanks for replying, my last year (sameperiodlast year) works fine. I am trying to see if there is a way to alter this measure or a new measure to show the year before last...

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Please check PARALELLPERIOD function then, it should work 🙂 

       

      PARALLELPERIOD function (DAX) - DAX | Microsoft Docs

       

      Your interval should be -2 for example:

      CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PARALLELPERIOD(DateTime[DateKey],-2,year)) 

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Jitmondo ,

     

    You could try use edate() function. Please check this measure.

    measure =

    var _date =selectedvalue('date'[date])

    return
    calculate(sum([value]),filter(allselected('table'),'table'[date]>=date(year(_date)-2,1,1)&&'table'[date]<=edate(_date,-24)))

    Best Regards,

    Jay