Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

 

Thanks,

GA

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Anonymous 
    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.



3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,Anonymous ,

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous ,

       

      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 

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Anonymous 
        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.