6 Replies Latest reply on Feb 7, 2019 2:32 AM by ewa.mckillop

    YTD, Previous YTD, etc calculation

    ewa.mckillop

      Hello

       

      I'm trying to create a calculation to be able to get to Year to Date, Year to date Last Year , Year to date 2 Years Ago etc based on the selected date.

       

      Example: When I select 29/09/2018 (from parameter 'Choose a date').

      I will get a table with: Year To Date (which is 01/01/2018-29/09/18), YTD Last Year (01/01/2018-29/09/2017) and YTD Two Years Ago (01/01/2016-29/09/2016) .

       

      I should mention that the raw data is cut weekly, so it won't ideally align to 29/09 every year but it should be the closest date to that.

       

      I will appreciate your help

       

      I've done this calculation but something is missing:

       

      STR([Choose a date] <= MAX([Date]) AND

      DATETRUNC( "year", [Choose a date]) = DATETRUNC("year", max([Date]) ))