1 Reply Latest reply on Oct 18, 2018 10:43 AM by patrick.byrne.0

    Combine different measures that are overlapped

    Arnau Forne

      Hi everyone,


      I'll try to explain several problems I have, all of them related.


      I haved 2 measures: Sales and Sales PY split by month. The reason for that is that each month the amount of stores opened varies and, therefore, we are trying to get a evolution at constant store level.

      The problem is that I have two different sales value for the same month (the one in measure Sales and the one in measure Sales PY).

      For 2018 is ok to take sales from measure Sales but for the rest I need to grab Sales PY column



      I think I've solved the problem at YTD level creating different Date Maseures:


      For 2018 - TEST 2018


      IF [Month] <= [End Date 2018]

      AND DATEDIFF('year',[Month],[End Date 2018])= 0 THEN [Daily Organic Sales] END


      For 2017 and 2016 - TEST 2016 and TEST 2017


      IF [Month] <= [End Date 2017]

      AND DATEDIFF('year',[Month],[End Date 2017])= 0 THEN [Organic Sales p.y (week not completed)_MTD] END


      but PROBLEM 1: I don't know how to create a similar output at Moving Average Time (MAT), for example MAT Jul'18 which would include data from Sales measure (until Jan'18) and Sales PY data from Dec'17 to Aug'17)


      PROBLEM 2: Once I've solved the first problem, is there any way to put together all 3 measures, bring them into Dimensions and use them as a DATE dimension?


      Please, find attached my workbook.


      Thanks in advance!