Trying to create a table which has specific dates, sales, and sales for the day one week before.

[SingularitySong]

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?

[v-xiaotang]

Hi @SingularitySong 

Thanks for reaching out to us.

>>but not for individual days, which is what I need.

Do you mean you want sales for  individual days? Can you provide some sample data and the expected result of it?

Thanks. I need some data and expected values to customize the measure.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.