Based on current week selection in Slicer Previous 6 weeks data display under line/bar chart

[Anonymous]

I'm Having Year Slicer, In that i have data for Years

e.g

Slicer 1st data as below

2017

2016

and second slicer is for weeks for those years

e.g having below data

Slicer 2nd data as below

02-01-2017

09-01-2017

16-01-2017

23-01-2017

30-01-2017

06-02-2017

13-02-2017

15-02-2016

22-02-2016

29-02-2016

07-03-2016

14-03-2016

21-03-2016

28-03-2016

Suppose user selects any week from above weeks list in the slicer , In another chart i.e Line or bar chart Previous 6 weeks data will be display means suppose user selected week here is 09-01-2017 then in another chart i.e line\bar data displays upto Previous 6 weeks data

i.e from 16-01-2017 to 15-02-2016 and its is work as dynamic solution. help really appricate and revert if have any solution on that as i'm new in Power BI

[Eric_Zhang]

@Anonymous

For this case, I think you need a calendar table as below. And then create a one to many relationship between your table and dim_date.

dim_date = 
VAR TBL =
    FILTER (
        ADDCOLUMNS (
            CALENDAR ( "2016-01-01", "2017-12-31" ),
            "WEEKDAY", WEEKDAY ( [Date] )
        ),
        [WEEKDAY] = 7
    )
RETURN
    ADDCOLUMNS ( TBL, "RANK", RANKX ( TBL, [Date],, ASC ) )

To get the data for last 6 weeks, create a measure like

measure =
SUMX (
    FILTER (
        ALL ( yourTable ),
        dim_date[RANK] <= MAX ( dim_date[RANK] )
            && dim_date[RANK]
                >= MAX ( dim_date[RANK] ) - 6
    ),
    [somecolumn]
)

[Anonymous]

Coluld you please explain me more this solution so i can used in that in my file.

[Anonymous]

@Eric_Zhang Thanks to reply on my Post, I'm new in Power BI and DAX coluld you please explain more brifly how its work's when i selectd current year in slicer it returns 6 weeks data under Line chart including current week. Please explain More its helps me.

[Eric_Zhang]

@Anonymous

I think the solution shall work. Have you tried in your case? If you can post the sample in your case, I can explain the DAX accordingly.

[Anonymous]

Thanks @Eric_Zhang i tried another Solution and that's works for me . Thanks for your reply and help.

[Eric_Zhang]

@Anonymous

Could you share your approach and accept it as solution to close this thread? People who have the similar question can benenif from the solution. 🙂 

[Anonymous]

Hello @Eric_Zhang

Sorry for delay in reply Actully i was on leave for few days.

Hi @Eric_Zhang My Solution works only when there is no any relationship between 2 tables. You can checked in below images i i have selected weeknumbers 12 and in line chart data display from week numbers 12 to week numbers 6. As this soution working Properly.  

Working when there is no relation into two tables

 but when i have added Datekey into both tables and Datekey here is Index column which makes active relationship into both tables then above solution is not working. i want above scenario working when two tables or more than 2 tables having

relationship between them. 

 As in your solution you have rank column. Guide me on this Approach

[Anonymous]

Hi

If possible please write dax here for below solution?

[Anonymous · Accepted solution]

@Eric_Zhang  I gone through the below link and its works for me.

http://community.powerbi.com/t5/Desktop/Show-the-last-X-years-based-on-the-current-Selection-of-the-...

Thanks