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
gavenks2024
Frequent Visitor

How to Dynamically get 13 weeks (FY Week) based on Year selection in Slicer ?

Hi Community, 

I have a requirement where i need to show only 13 weeks based on the Year selected in slicer. visuals shown below are part of my requirement. 

 

When i select 2024 in date slicer,  i expect to only see 13 weeks which is from FY24-W02 to FY24-W14 (13 weeks ) . Similarly if 2023 is selected only, based on period selection, Rolling 13 FISCAL WEEKs based on date selection should be shown. I have dim columns like FY week , fiscal week start date, end date. However, I am not finding a way to dynamically show 13 weeks based on filter selection. 

 

Please assist. 

 

Hierarchical Slicer (starts with year)Hierarchical Slicer (starts with year)Visual requirement to show 13 weeksVisual requirement to show 13 weeks

 

Thanks,

GA

1 ACCEPTED SOLUTION

@gavenks2024 
Hello, you can calculate this by directly creating a measure:

M_result = 
VAR FirstDayMAT=
MIN('DateTable'[Date])
VAR EndDayMAT=
FirstDayMAT+(7*13-1) // Date values from the beginning of the selected year (1/1) to the thirteenth week
RETURN
CALCULATE(MAX('DateTable'[FY Week]),FILTER(ALLSELECTED(DateTable),'DateTable'[Date]>=FirstDayMAT&& 'DateTable'[Date]<=EndDayMAT))

vjtianmsft_0-1715335724499.png

like this:

vjtianmsft_1-1715335751738.png

 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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



View solution in original post

3 REPLIES 3
v-jtian-msft
Community Support
Community Support

Hi,@gavenks2024 ,

I am gald to help you.
According to your description, you want data for the first 13 weeks of the selected year.
If you need to filter using a custom column you created yourself, FY Week.
You can refer to the method below:
Here I have also defined a column to simulate your data.

FY Week = 
 "FY"&'DateTable'[YearNum]&"-W"&'DateTable'[WeekStart]

vjtianmsft_0-1715313682478.png

I got the year and the serial number of week.

vjtianmsft_1-1715313702832.png

WeekNum = VALUE( RIGHT([FY Week],2))



Here you choose to filter the data based on the newly created weekNum, filtering out the selected months with values less than or equal to 13
Then the YearNum is used as a slicer
Like this:

vjtianmsft_2-1715313770981.png

 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

Hi @v-jtian-msft ,

 

Thanks for your response . I have already tried a similar solution you proposed here. However, We cannot make use of filter pane here. Reason being, I want first 13 weeks based on the Year that is selected in Slicer . So Whole data in visual is now controlled through the slicer. 

 

Hope I made it clear. 

 

Thanks,

GA 

@gavenks2024 
Hello, you can calculate this by directly creating a measure:

M_result = 
VAR FirstDayMAT=
MIN('DateTable'[Date])
VAR EndDayMAT=
FirstDayMAT+(7*13-1) // Date values from the beginning of the selected year (1/1) to the thirteenth week
RETURN
CALCULATE(MAX('DateTable'[FY Week]),FILTER(ALLSELECTED(DateTable),'DateTable'[Date]>=FirstDayMAT&& 'DateTable'[Date]<=EndDayMAT))

vjtianmsft_0-1715335724499.png

like this:

vjtianmsft_1-1715335751738.png

 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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



Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 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