9 Replies Latest reply on Oct 5, 2016 6:45 PM by Shinichiro Murakami

    MTD & YTD calculation

    Vishal D

      Attached is the twbx. I have couple questions/issues.

       

      I want to create MTD & YTD columns for each Measure.

       

      YTD - Year to Date should show sum of all month based on 'Report Year' selected in filter

      MTD - Month to Date should show data only for the selected 'Report Month'

       

      Eg. I need 'YTD Store Sales MB' & 'MTD Store Sales MB'. If 'Report Year' = 2016 and 'Report Month' selected = 6 then

       

      'YTD Store Sales MB' = SUM of all months of 2016 (ignore the month filter)   (if they select prior years e.g 2015 then sum of all months Jan to Dec)

      'MTD Store Sales MB' = 'Store Sales MB' for just 6th month for 2016 (Take into consideration selected 'Report Month' & 'Report Year' in the quick filters.

       

      There is another measure for which I need AVG - 'YTD Turnaround %' = AVG of all months of 2016 (ignore the month filter)

      'MTD Turnaround %' = actual 'Turnaround %' for that month and year

       

      User can select only one year and month from filter(single value)