Trying to chart a rolling 6 week percentage by fiscal week

[rheimbr]

I have three measures, that I would like to have work together, but currently when the third one is run, it returns blanks. I am sure that I have something incorrect in how I have my time-intelligence set up, but I have been unable to figure out how to do it properly.

The goal is to have a base performance measure, a measure that gives the performance for the last six weeks from the last active date, and then a third that iterates both of the first two for the last six weeks to show trends.  

MEASURE1 is the base measure, and it calculates the number of successful units that have delivered and contains some filters for both the numerator & denominator. The version I'm posting uses SUMX, but I've also tried it set up around a SUM formula as well.

MEASURE1 =
IF (
    [Planned Stocked Units] <> BLANK (),
    1
        - CALCULATE (
            SUMX ( IB_Tracker, IB_Tracker[Total Shipped Qty] ),
            IB_Tracker[DayDiff] > 0,
            IB_Tracker[INDC] <> "OT",
            IB_DC[FranType] <> "Direct"
        )
            / CALCULATE (
                SUMX ( IB_Tracker, IB_Tracker[Total Shipped Qty] ),
                ALL ( IB_Hierarchy ),
                IB_DC[FranType] <> "Direct"
            )
)

MEASURE2 returns the desired result on a single card, but if you iterate it out over weeks by itself, it returns the same values as MEASURE1 for each fiscal week. This is the measure that I think needs help, but it could be others do as well.

MEASURE2 =
CALCULATE (
    [MEASURE1],
    ALL ( 'Calendar' ),
    FILTER (
        'Calendar',
        'Calendar'[Date]
            >= LASTDATE ( 'Calendar'[Date] ) - 41
            && 'Calendar'[Date] <= LASTDATE ( 'Calendar'[Date] )
    )
)

MEASURE3 takes MEASURE1 or MEASURE2 and calculates it out over the last 6 fiscal weeks. This works just fine with MEASURE1, but when I swap in MEASURE2, it only returns blanks. This measure is used for a line chart in a tooltip as you hover over a card with just the base measure.

A second calendar table was utilized to aid in getting all 6 weeks to show, the page contains a slicers for fiscalyear,fiscalmonth, fiscalweek, & date range.  I'd be happy to know how to get it to work without a second date table if possible, but that is less of a concern. I trie dit with the filters pointing back at the normal calendar, but if someone selects a month in the slicer it cuts out any of the 6 weeks that fall outside of that period.

MEASURE 3 =
VAR EndDate =
    LASTDATE ( 'Calendar'[Date] )
VAR StartDate =
    EndDate - WEEKDAY ( EndDate, 1 ) - 35
RETURN
    CALCULATE (
        [MEASURE1 or MEASURE2],
        ALL ( 'Calendar' ),
        FILTER (
            'Calendar2',
            StartDate < 'Calendar2'[Date]
                && 'Calendar2'[Date] <= EndDate
        )
    )

Here ais a table of the results if I select a date range that encompasses week 17-22, which is why Measure 1 & 2 currently match. The only other thing I can think of that is relevant at this point is that we start our fiscal year in February.

Fiscal Week	MEASURE1	MEASURE2	Current MEASURE3 (applied to MEASURE2) results	Desired MEASURE 3 (applied to MEASURE2) results
17	89.78% (week 17 only)	89.78% (week 17 only)		80.20% (weeks 12-17)
18	67.11% (week 18 only)	67.11% (week 18 only)		77.20% (weeks 13-18)
19	66.65% (week 19 only)	66.65% (week 19 only)		74.50% (weeks 14-19)
20	73.96% (week 20 only)	3.96% (week 20 only)		75.07%(weeks 15-20)
21	83.91% week 21 only)	83.91% week 21 only)		74.54% (weeks 16-21)
22	78.69% (week 22 only )	78.69% (week 22 only )		73.82% (weeks 17-22)
Totals (Single Card Value)	73.82% (weeks 17-22)	73.82% (weeks 17-22)		N/A

Thanks in advance, and let me know if I can clarify anything further to help resolve this.

[Stachu · Accepted solution]

that should fix measure 2, assuming the fiscal week in the visual is from Calendar table

MEASURE2 =
VAR __LastDate = LASTDATE ( 'Calendar'[Date] ) 
RETURN
CALCULATE (
    [MEASURE1],
    FILTER (
       ALL( 'Calendar' ),
           'Calendar'[Date] >=__LastDate  - 41
            && 'Calendar'[Date] <= __LastDate 
    )
)

why do you need 2 calendar tables? you should be able to achieve everything with just 1, I really cannot think of a good reason to add a second one

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

[rheimbr]

Thanks so much for your help! I think what you've done is fix the issues I was having with MEASURE3. The second calendar table predates me and I was trying to avoid it, but without much luck. I see better how the ALL/FILTER functions work no though, that's really useful.

Due to some slicers we are unable to remove from the page currently, the second variable for the start date is still needed, but I was able to reincorporate that into your code without any errors.

These measures are being written for some line graphs and we always want to see the current week to date, and the five full weeks before it, so that second variable helps avoid that first week of the six showing up as partial results.

So to this point, we are getting the last 6 weeks broken out by individual week performance, the piece I am still trying to solve for is if we want to get the rolling performance, so that each fiscal week on the chart represents that fiscal week + the 5 full fiscal weeks prior as well. Any further advice on how to do that, be it in a fresh measure or one that points to either MEASURE1 or the update MEASURE2 is still welcome.

Finally, here is the new version of MEASURE2, rendering MEASURE3 redundant.

MEASURE2 = 
VAR EndDate = LASTDATE ( 'Calendar'[Date] )
VAR StartDate = EndDate  - WEEKDAY ( EndDate, 1 ) - 34
RETURN
CALCULATE (
    [MEASURE1],
    FILTER (
       ALL( 'Calendar' ),
           'Calendar'[Date] >= StartDate
            && 'Calendar'[Date] <= EndDate
    )
)

[Stachu]

how do you define a full fiscal week? are there any special rules for holidays, or just Mon-Sun?

wouldn't it be similar syntax to current measure 2, only where EndDate is the beggining of current week -1?
I must say I cannot really visualize what you're trying to achieve

[rheimbr]

Hi, I've bolded the main request, but I've tried to explain some of the context a bit. I hope this helps! 

Our fiscal weeks run Sun-Sat with no exceptions for holidays/weekends, so if that second variable isn't there, and the last date on the filters is something like a Wednesday, going back 41 days will not capture the data from sunday-tuesday of the earliest week in the six weeks. 

To anwser your other question maybe this will help. One additional bit of info I may have forgotten above, is that we currently include both fiscal week/month/year slicers as well as a date range slicer. Moving between the two creates enough headaches, that I would prefer to eliminate the date range slicer, but the end users are not on board with that. 

Right now MEASURE1 is the base measure, and it is written so it will pull in the results for the whatever period is defined by filters/slicers. So, if it were a card and I selected June in the slicers, that number would be for June only. Using the image below instead, (FW 17-22),  Week 22 is 78.69%, but the total for the selected period is 73.82%

MEASURE2 takes that and just makes sure that the last 6 weeks worth of results are showing (hypothetical situation - With June 2019 Selected, MEASURE1 would only show 5 weeks, but MEASURE2 will show 6. Similarly If we open up the report filters to May-June, now MEASURE 1 would bring in 9 weeks, and MEASURE 2 would still only bring in 6) 

I lined up the weeks in my original example so that it was for 6 weeks, so again Week 22 is Showing 78.69 and the Total is 73.82.  What I want in a third measure, is that Total for the 6 weeks to be the new result for Week 22 only, and then for week 21, it would be the total from weeks 16-21, and so on.  

PLease let me know if this clarifies anything for you.