Restrict results to a 6 week period for trend charts

[rheimbr]

I'm trying to write a measure for charts that limits the results to the 6 most recent weeks (including current week depending on the filters applied in the filter pane) of results based on the latest date active in the calendar, and thus far I have been unable to do it without referencing a second calendar. Can anyone help me understand what is missing from the first sample formula that is necessary to get it working?

Example 1 When placed in a table that shows Fiscal Weeks, the weekly results are accurate, but they do not stay restricted to the 6 weeks. The subtotal result, does actually reflect just the 6 week period, however.

6 Week Trend Performance Success = 
var EndDate = LASTDATE('Calendar'[Date])
var StartDate = EndDate-WEEKDAY(EndDate,1)-35
RETURN
CALCULATE(
    [Performance Success],
    ALL('Calendar'), 
    FILTER(
        'Calendar', 
        StartDate < 'Calendar'[Date] && 'Calendar'[Date] <= EndDate 
    )
)

Results from Example 1

Example 2 - restricts to 6 weeks as desired, uses a duplicate table set up with the same relationships to the table the base measure  comes from.

6 Week Trend Performance Success = 
var EndDate = LASTDATE('Calendar'[Date])
var StartDate = EndDate-WEEKDAY(EndDate,1)-35
RETURN
CALCULATE(
    [Performance Success],
    ALL('Calendar'), 
    FILTER(
        'Calendar2', 
        StartDate < 'Calendar2'[Date] && 'Calendar2'[Date] <= EndDate 
    )
)

Results from Example 2

[v-lionel-msft]

Hi @rheimbr ,

How about using [FiscalWeek] column to limit the scope?

6 Week Trend Performance Success = 
VAR x = 
CALCULATE(
    MAX('Calendar'[FiscalWeek]),
    ALL('Calendar')
)
VAR y = x - 6
RETURN
CALCULATE(
    [Performance Success],
    ALL('Calendar'), 
    FILTER(
        'Calendar', 
        'Calendar'[FiscalWeek] <= x && 'Calendar'[FiscalWeek] >= y
    )
)

Best Regards,

Lionel Chen

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

[TomMartens]

Hey @rheimbr ,

in the first example LASTDATE('Calendar'[Date]) returns the last day filtered by the fiscal year column and the week column.

For this,  I'm not sure what you expect to filter by the FILTER Statement.

Maybe you want to try something like this:

CALCULATE(
	LASTDATE('Calendar'[Date])
	,ALL('Calendar'[Fiscal Week])
)

Hopefully, this is what you are looking for.

Regards,

Tom

[rheimbr]

thanks @TomMartens &  @v-lionel-msft I will try to see if either of these alternatives help sometime today and let you know!

[rheimbr]

Thanks again for your help, neither solution fully worked, but it did help me recognize that my variable for the enddate was not capturing what I had thought. I have the revised formula below, but I still have one piece that seems to be missing.

This works correctly so long as the date range selected is 6 weeks or longer, but if the date range is only 2 weeks, then only two weeks show up in the table or chart.

I tried adding ALL('Calendar'[Date]) before the Filter formula, but that did not have any effect. I also tried using it within the Filter formula, but that affected the full result and just repeated it for each week instead of calculating just that week

6 Week Performance Success = 
var EndDate = CALCULATE(max('Calendar'[Date]),ALLSELECTED('Calendar'[date]),ALLSELECTED('Calendar'[FiscalYear]),ALLSELECTED('Calendar'[FiscalWeek]))
var StartDate = EndDate-WEEKDAY(EndDate,1)-34
RETURN
CALCULATE(
    [Performance Success],
    FILTER(
        'Calendar', 
        'Calendar'[Date]>= StartDate && 'Calendar'[Date] <=EndDate 
    )
)

[v-lionel-msft]

Hi @rheimbr ,

Maybe because the value of  StartDate is fixed.

Best regards,
Lionel Chen

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