Forum Discussion
Based on current week selection in Slicer Previous 6 weeks data display under line/bar chart
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
9 Replies
Eric_ZhangMicrosoft Employee
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] )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.