Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Selected week and Last 4 Weeks Data Based on Selected Week

Hello Everyone,

 

I have a requirement where in I have to display the selected week data and last 3 weeks data in a bar chart.

Week slicer contains the values in format 

The required output look like this. If suppose I have selected 27 Nov-3 Dec, then the graph should look like this.

I have made few measures like 

Week Avg Prev Week  =
CALCULATE([AA_Week Avg],
FILTER(ALL('Dates Mapping'),
'Dates Mapping'[Index Week] = SELECTEDVALUE('Dates Mapping'[Index Week])-1 &&
'Dates Mapping'[YEAR] = SELECTEDVALUE('Dates Mapping'[YEAR])))
 
AA_Week Avg Prev 2 =
CALCULATE([AA_Week Avg],
FILTER(ALL('Dates Mapping'),
'Dates Mapping'[Index Week] = SELECTEDVALUE('Dates Mapping'[Index Week])-2 &&
'Dates Mapping'[YEAR] = SELECTEDVALUE('Dates Mapping'[YEAR])))
 
But I am unable to achieve the required output, Can anyone please help me resolve this issue.
 
 
 
Thanks.
waseem

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    I have used the following formula to reproduce a data sample:

    Table =
ADDCOLUMNS (
    CALENDAR ( "2020/1/1", "2020/12/31" ),
    "Week Number", WEEKNUM ( [Date], 2 )
)

     

    In order to create dynamical chart, I extracted the Week Number and Week columns to a new table for slicer, and clicked the Sort by column tab to sort the Week column by Week Number column like this:

    ForSlicer =
DISTINCT (
    SELECTCOLUMNS ( 'Table', "Week", [Week], "Week Number", [Week Number] )
)

     

    Moving forword, created a measure and applied it to filter pane:

    Measure =
VAR _sele =
    SELECTEDVALUE ( ForSlicer[Week] )
VAR _weekNum =
    MINX ( FILTER ( ALL ( 'Table' ), 'Table'[Week] = _sele ), [Week Number] )
RETURN
    IF (
        MAX ( 'Table'[Week Number] ) >= _weekNum - 3
            && MAX ( 'Table'[Week Number] ) <= _weekNum,
        1,
        0
    )

     

    The final output is shown below:

    Please kindly take a look at the pbix file here.

     

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

8 Replies

  • aj1973's avatar
    aj1973
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous 

    Your isssue is not exposed wisely! 

    what do you want to do with the above measures?

    This measure [AA_Week Avg] where is it?

    using SLECTEDVALUE! what value you want to select?

    Can you share a Sample of your PBIX please, this will help the community understand quicker and better

    Thnaks

     

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , relative date slicer is best in all such case, When you can not use you have take help two table, to do it.

    Refer to this example - This is for 12 months, but you have to use this for weeks

    https://www.youtube.com/watch?v=duMSovyosXE

     

    Check if needed

    Power BI — Week on Week and WTD
    https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
    https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
    https://www.youtube.com/watch?v=pnAesWxYgJ8

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    I have used the following formula to reproduce a data sample:

    Table =
ADDCOLUMNS (
    CALENDAR ( "2020/1/1", "2020/12/31" ),
    "Week Number", WEEKNUM ( [Date], 2 )
)

     

    In order to create dynamical chart, I extracted the Week Number and Week columns to a new table for slicer, and clicked the Sort by column tab to sort the Week column by Week Number column like this:

    ForSlicer =
DISTINCT (
    SELECTCOLUMNS ( 'Table', "Week", [Week], "Week Number", [Week Number] )
)

     

    Moving forword, created a measure and applied it to filter pane:

    Measure =
VAR _sele =
    SELECTEDVALUE ( ForSlicer[Week] )
VAR _weekNum =
    MINX ( FILTER ( ALL ( 'Table' ), 'Table'[Week] = _sele ), [Week Number] )
RETURN
    IF (
        MAX ( 'Table'[Week Number] ) >= _weekNum - 3
            && MAX ( 'Table'[Week Number] ) <= _weekNum,
        1,
        0
    )

     

    The final output is shown below:

    Please kindly take a look at the pbix file here.

     

    Best Regards,
    Eyelyn Qin
    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
      5 years ago

      Hi Anonymous ,

       

      the sample pbix file is no longer available. Can you please be so kind and upload it again?

       

      Regards,

      Toddy

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks amitchandak  and Anonymous  I am able to achieve the required output,

    But is there a way, where I can use the slicer week from the main date table or the values tables instead of taking it from new table, as I have other charts and tables on the page that should interactive with the slicer.

     

     

    Thanks

    Waseem

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous ,

       

      No.

      If you use the original column in table for slicer and then for example ,select 10 Feb-16 Feb.

      In this case, only date between this range will be displayed. , it is impossible to filter out the values of the previous three weeks which is why we need to create a separate table.

       

      Best Regards,
      Eyelyn Qin
      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
    5 years ago

    Hi Anonymous ,

     

    Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

    Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business after removing sensitive data.

     

    Best Regards,
    Eyelyn Qin

  • Jyo27's avatar
    Jyo27
    Icon for Helper II rankHelper II
    3 years ago

    I have the same issue . Its working for me this solution . But when I clear the selection for week slicer I need all the week data . How can I achieve this?