This is a very strange one, but I’ve actually had to deal with this issue several times and I’m not sure how to do it.
This is a DAX problem, but I’m going to use Excel to illustrate:
I need to calculate Average Sales by WEEK over 3 previous quarters. My fiscal calendar has 13 weeks; we’re on a 4-4-5 fiscal calendar so there are 13 weeks in each quarter. I have this measure and it works well.
My problem is this: For example, if we’re in Week 12 in the CURRENT quarter, I need to take the average for week 12 for the previous 3 quarters (which is $712,616) and display it for the current month. When I try to do this, I get 0’s, because the measure is only going to show the values for week 12 for each of the actual quarters where the sales occurred (2017-Q4 12/24-12/30, 2018-Q1 3/18-3/24, 2018-Q2 6/17-6/23).
I need to take that weekly average $712,616 and save it so it’s not attached to the dates that helped calculate it and show it in the current month. See below.
I tried creating a separate date table, but it got very messy and ultimately couldn’t get it to work.