I'm trying to create a matrix which has a list of individual dates (not a date hierarchy), sales numbers for those dates, and then in the third column, sales numbers for the date seven days before the date in column one. There's also going to be more columns including sales for the date 14 days before, 21 days before, 7 days after, and so on. Basically the same day of the week in previous and next weeks. I'm creating a measure to calculate this, as I need it to be sliceable. I have tried everything I can think of and looked everywhere I can, bothing works. A few examples which seemed closest below:
Sales_7_days_ago = calculate (distinctcount(sales_reference), dateadd(sales_date, -7, day) : this one does give me figures for the year, quarter, and month when using the date hierarchy, but not for individual days, which is what I need. If I drill down into days, or use date as a date rather than a date hierarchy, it just goes completely blank.
Sales_7_days_ago = calculate(distinctcount(sales_reference), selectedvalue(sales_date) = calculated_date_seven_days_before_sales_date : this one just gives me data for the very start of my period. Don't know why.
Would anyone be able to help?
