SelectedValue in a filter

[densou]

This maybe a little complicated to understand.

1. I want to filter my results by period. (To figure out my performance index)

1. My performance index is base on the last 3 periods.

1. To do that I need to do the SUM(*******) of my product, and then FILTER(My period = SELECTEDVALUE - 1 then repeat with -2 then repeat with -3) to finally do an average of all three of my periods. (The average work's fine)

The result alway's comes out blank, but If I put my filter like this (Let's say we are currently period 10) FILTER(My period= 10-1 or 2 or 3) it work's. 

The SELECTEDVALUE would of course be the slicer of my selected period, so in this example, 10. 

Thanks

[Anonymous]

// Assumptions:
// 1. There is a DIMENSION, Period, that stores periods.
// Each period has a unique PeriodId and those
// are increasing by 1.
// 2. All dimensions are connected to your fact table
// via 1:* and filtering is one-way only and slicing
// is only done via dimensions (all data columns in
// the fact table are hidden).
//
// Since I don't have any more information, this is
// the best I can do. I think you have a Calendar
// in our model, so if you do, then you should state
// how you want our measure to work with granularities
// higher/lower than your periods. You have not stated it
// here.

[Measure] =
var __onePeriodSelected = HASONEVALUE( Period[PeriodId] )
var __selectedPeriod = SELECTEDVALUE( Period[PeriodId] )
var __sumForLast3Periods =
	CALCULATE(
		SUM( FactTable[Amount] ),
		Periods[PeriodId] < __selectedPeriod,
		Periods[PeriodId] >= __selectedPeriod - 3,
		ALL( Periods )
	)
var __avg =	__sumForLast3Periods / 3
var __thereAreEnoughPeriods =
	CALCULATE(
		min( Period[PeriodId] ),
		all( Period )
	) <= __selectedPeriod - 3
var __shouldCalculate =
	__onePeriodSelected && __thereAreEnoughPeriods
return
	if( __shouldCalculate, __avg )

Best

D

[Greg_Deckler]

Could be made easier to understand. Sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490