cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Restrict results to a 6 week period for trend charts

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

5 REPLIES 5
Community Support

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.

Super User

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Frequent Visitor

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

Frequent Visitor

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
)
)``````

Community Support

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.