Last N Weeks based on week slicer selection not working?

[itsmebvk]

Hi Folks,

I have Three tables in AAS cube Fact, Date Dim and Dim. I am trying to create last 4 weeks (Including current week selected) based yearweek slicer selcted in slicer. Problem here is I have live connection to AAS cube, In my cube Yearweek column marked as text instead off Number so I am using following small code to get last 4 weeks based on slicer selection. However I am getting only one week based in slicer value. Is there anything I am missing? Please suggest?

Calc_new = CALCULATE(SUM(Fact [Measure]),FILTER(Dim_Date,VALUE(Dim_Date[Calendar Year Week])>=VALUE(SELECTEDVALUE(Dim_Date[Calendar Year Week]))-4 && VALUE(Dim_Date[Calendar Year Week])<=VALUE(SELECTEDVALUE(Dim_Date[Calendar Year Week])))) 

[amitchandak]

@itsmebvk , Try like

Rolling 4 week =
VAR _maxWeek = MAXX(allselected(Dim_Date),Dim_Date[Calendar Year Week])
Var _maxYear = MAXX(allselected(Dim_Date),Dim_Date[Calendar Year])
VAR _minWeek = if(_minWeek <4, 52 + _maxWeek-4, _maxWeek -4)
Var _minYear = if(_minWeek <4, _maxYear -1 , _maxYear)
RETURN
CALCULATE(SUM(Fact [Measure]),FILTER(Dim_Date, Dim_Date[Calendar Year Week] <= __maxWeek && Dim_Date[Calendar Year] <= _maxYear &&
Dim_Date[Calendar Year Week] >= _minWeek && Dim_Date[Calendar Year] <= _minYear))

[itsmebvk]

@amitchandak Thanks for your quick reply.

i tried the solution provided above but I am still getting one week? Anything I am missing?

Rolling 4 week =
VAR _maxWeek = MAXX(allselected(Dim_Date),Dim_Date[Calendar Year Week])
Var _maxYear = MAXX(allselected(Dim_Date),Dim_Date[Calendar Year])
VAR _minWeek = if(_minWeek <4, 52 + _maxWeek-4, _maxWeek -4)
Var _minYear = if(_minWeek <4, _maxYear -1 , _maxYear)
RETURN
CALCULATE(SUM(Fact [Measure]),FILTER(Dim_Date, Dim_Date[Calendar Year Week] <= __maxWeek && Dim_Date[Calendar Year] <= _maxYear &&
Dim_Date[Calendar Year Week] >= _minWeek && Dim_Date[Calendar Year] <= _minYear))

I just replaced Minweek variable 

 

[itsmebvk]

Hi Folks,

Any other thoughts or suggestions around above isssue please?

Regards

bvk

[itsmebvk]

Thanks for your inputs. Finally I am able to achieve this usin Calculation groups as there are not many options to do in Live connection.

[v-deddai1-msft]

Hi @itsmebvk ,

I'm glad that you have solved the issue by yourself. But I think there is only one filter is wrong in your original formula, please refer to 

Calc_new = CALCULATE(SUM(Fact [Measure]),FILTER(ALL(Dim_Date),VALUE(Dim_Date[Calendar Year Week])>=VALUE(SELECTEDVALUE(Dim_Date[Calendar Year Week]))-4 && VALUE(Dim_Date[Calendar Year Week])<=VALUE(SELECTEDVALUE(Dim_Date[Calendar Year Week])))) 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[itsmebvk]

@v-deddai1-msft  Thanks for your inputs.

Even after using following expressions I am only getting one week as I have YYYYWW slicer, what ever value I select there only that week is being displayed in list. 

if  I remove interaction between slicer and list its showing all weeks.

Can you please correct me.

FYI I would like to highlight few things again

1)It is live connection to AAS Model

2)I am creating meaure

3) my YYYYWW now its number format.

Calc_new2 = CALCULATE(SUM(Fact [Measure]),FILTER(ALL(Dim_Date),VALUE(Dim_Date[Calendar Year Week])>=VALUE(SELECTEDVALUE(Dim_Date[Calendar Year Week]))-4 && VALUE(Dim_Date[Calendar Year Week])<=VALUE(SELECTEDVALUE(Dim_Date[Calendar Year Week]))))

Calc_new3 = CALCULATE(Fact [Measure]),FILTER(ALL(Dim_Date),Dim_Date[Calendar Year Week]>=SELECTEDVALUE(Dim_Date[Calendar Year Week])-4 && Dim_Date[Calendar Year Week]<=SELECTEDVALUE(Dim_Date[Calendar Year Week])))

 
Please suggest.

[v-deddai1-msft]

Hi @itsmebvk ,

What's your relationship between your dim_date table and fact table? Are they connected by [Calendar Year Week] column? The formula behavior well in my sample data.

Best Regards,

Dedmon Dai

[itsmebvk]

@v-deddai1-msft Apologies for not explaining properly earlier.

in you example its showing measure for last 2+current date which is good, but its showing only one date which selected.

The requirement is when I select a week in slicer, matrix should show selected week and last two weeks in columns and measure for each week in measures.

[Ashish_Mathur]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

[itsmebvk]

@Ashish_Mathur Thanks for your reply. I am sorry suggested work around didn't help.

[v-deddai1-msft]

Hi @itsmebvk , 

Are you trying to show last 4 weeks in the rows of matrix and show sum value of last 4weeks in each rows?

You need to disable relationship between dim_date and fact table. Use the calendar year week column from your fact table as slicer. Then create a visual level filter for your matrix table(all the measure are based on my sample data).

visualfilter = IF(MAX(Dim_Date[Date])>=MAX('Table'[Date])-2&&MAX(Dim_Date[Date])<=MAX('Table'[Date]),1,0) 

set it as 1 in matrix:

Then use the following measure:

Measure = var a = CALCULATETABLE(VALUES(Dim_Date[Date]),FILTER(ALL(Dim_Date),Dim_Date[Date]>=MAX(Dim_Date[Date])-2&&Dim_Date[Date]<=MAX(Dim_Date[Date]))) return CALCULATE(SUM('Table'[value]),TREATAS(a,'Table'[Date]))

Please refer to my sample pbix .

[itsmebvk]

@v-deddai1-msft @Ashish_Mathur @amitchandak 

I am so sorry I tried all the ways nothing really worked for me. Whenever I select any week in slicer my matrix or table showing only that selcted week. 

As an alternative I changed my report to Import mode. Now whenever I select any week (YYYYMM format) in drop down slicer, I would like to show Last four weeks including selected week in columns and  states in rows any measure in measure place. Can you please suggest how we can achieve this?

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Ensure that in the Calendar table, there is a Year week column.

[itsmebvk · Accepted solution]

@Ashish_Mathur @v-deddai1-msft @amitchandak 

Finally the options highlighted worked to achieve the requirement.

 

 

Thank you so much for spending time and giving inputs.

[itsmebvk]

@Ashish_Mathur  I am extremely sorry I dint have access to upload file.  If possible can we please use the same file which @v-deddai1-msft reply.