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

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.