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-...
Eric_Zhang
Microsoft Employee
Microsoft EmployeeAnonymous
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]
)
Coluld you please explain me more this solution so i can used in that in my file.