Selected week and Last 4 Weeks Data Based on Selected Week

[waseem_24]

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

[Jyo27]

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?

[v-eqin-msft]

Hi @waseem_24 ,

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

[waseem_24]

Thanks @amitchandak  and @v-eqin-msft  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

[v-eqin-msft]

Hi @waseem_24 ,

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.

[v-eqin-msft · Accepted solution]

Hi @waseem_24 ,

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]

Hi @v-eqin-msft ,

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

Regards,

Toddy

[amitchandak]

@waseem_24 , 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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[aj1973]

Hi @waseem_24 

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