Dynamic Display Columns when Fiscal Week selected in slicer(Rolling 4 Weeks Columns)

[Anonymous]

Dear All,

Need help - please refer below screenshot with complete details

[v-shex-msft]

Hi @Santosh_kumar,

You can refer to following steps if they suitable for your requirement.
Steps:
1. Create new calculated table.

Fiscal week = DISTINCT(Product[Fiscal Week]).

2. Create new measure.

Measure =
VAR currWeek =
    SELECTEDVALUE ( Product[Fiscal Week] )
RETURN
    IF (
        AND (
            currWeek
                >= MIN ( 'Fiscal week'[Fiscal Week] ) - 3,
            currWeek <= MIN ( 'Fiscal week'[Fiscal Week] )
        ),
        SUM ( Product[Pct] ),
        BLANK ()
    )

Note: “Product” table is the original table I defined.

If above not helps, please share some sample data for test.

Best Regards,

Jack

[Anonymous]

Hi @v-shex-msft , i have tried using above logic but unable to get dynamic level changes, screenshot below -

here is the dataset and pbxi file, click on hyperlink below -

Dataset  & Destop File

[v-shex-msft]

Hi @Santosh_kumar,

I attached my test file below, you can take a look on it.

Best Regards,

Jack Chen

[Anonymous]

HI @v-shex-msft,

The above isnt is working, below are the link to download dataset and pbix file, 

Can you please help and use numerator and denominator for Percentage calc

here is the dataset and pbxi file, click on hyperlink below -

Dataset  & Destop File

Thanks,

Santosh

[Anonymous]

Sorry, here is the dataset and pbxi file @Dataset - https://drive.google.com/open?id=1MQ1_swrTKf1UVfBrqxwZOHASXIBmHFeu and @PowerBI Destop File - https://drive.google.com/open?id=1nwgavaIcRvDZrJmBPH-kw4jScee22poP

[amitchandak]

This one works, when we do not select anything for the last 4 weeks. calc can be changed. I doubt because of single table remove the filter, all is not working, when we select a value

Efficiency_% = var _selweek = maxx(Sheet1,SELECTEDVALUE(Sheet1[Fiscal Week]))
var _Year = left(_selweek,4)
Var _week =RIGHT(_selweek,2)
var _weeknew = if(_week-4>0,_week-4,_week-4+52)
var _YearNew = if(_week-4>0,_Year,_Year-1)
Var _start =(_YearNew&_weeknew)+0
Var _end = _selweek+0
return  CALCULATE(DIVIDE(SUM(Sheet1[Numerator])*1.0,[Denom]),all(Sheet1[Fiscal Week]),FILTER(all(Sheet1[Fiscal Week]),Sheet1[Fiscal Week]>_start && Sheet1[Fiscal Week]<_end))

[Anonymous]

 Hi @Sami -  Its not working, getting single column