DAX formula for past 5 weeks from selected week reg,.

[Meerakhanna]

Hi, (Kindly let me know if the below approach is doable in PBI)

I have Dim_Week table with following columns,

Calendar Year	Week	Week day	Week Order
2016	53	12/26/2016	53
2016	52	12/19/2016	52
2016	51	12/12/2016	51
2016	50	12/5/2016	50
2016	49	11/28/2016	49
2016	48	11/21/2016	48
2016	47	11/14/2016	47
2016	46	11/7/2016	46
2016	45	10/31/2016	45
2016	44	10/24/2016	44
2016	43	10/17/2016	43
2016	42	10/10/2016	42
2016	41	10/3/2016	41
2016	40	9/26/2016	40
2016	39	9/19/2016	39
2016	38	9/12/2016	38
2016	37	9/5/2016	37

I Have a fact table but not linked with the above Dim_Week. Instead I want to create a table based on above Dim_Week which will hold only filtered values of Dim_Week. 

I have slicers created based on Dim_Week. Lets say the User selects Year = 2016 and Week as 53, then the new table should hold the values for past 5 weeks of that selected week. like the below,

Calendar Year	Week	Week day	Week Order
2016	53	12/26/2016	53
2016	52	12/19/2016	52
2016	51	12/12/2016	51
2016	50	12/5/2016	50
2016	49	11/28/2016	49

I will link this table with my fact to display values pertaining to the [week day] here in the new table.

I am naive in DAX. Can anyone help me with DAX for the new table? - Thank you!

[v-shex-msft · Accepted solution]

Hi @Meerakhanna,

Power bi not support to create dynamic calculated column/table based on slicer.

Calculated Column/Table Change Dynamically According to Slicer Selection in the Report.

For your requirement, I'd like to suggest you use query parameter to create the dynamic filter calendar table.

Steps:
Duplicate the original calendar table and use the parameter as the dynamic filter.

Reference link:

Deep Dive into Query Parameters and Power BI Templates

Using dynamic parameter values in Power Query Queries

Regards,

Xiaoxin Sheng