Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
rheimbr
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

bad results.PNG

 

 

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

good results.PNG

 

5 REPLIES 5
v-lionel-msft
Community Support
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.

 

TomMartens
Super User
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

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

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

 

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.