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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors