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

[gavenks2024]

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)Visual requirement to show 13 weeks

Thanks,

GA

[v-jtian-msft]

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]

I got the year and the serial number of week.

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:

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.

[gavenks2024]

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 

[v-jtian-msft · 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))

like this:

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.