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!
It looks like the workbook did not attach to the post. Please try and reattach the workbook to the post so that a Community memeber may better assist with the unexpected behavior.
Hope this helps!