Average Weekly for Previous Year with Partial Independence of Date Slicer

[StephenK]

Hello all,

I am trying to create a measure that calculates the average number of weekly visits for the year prior to whatever year is selected by the year slicer. The part I am struggling with is this--I'm trying to visualize my data by "Week in Year" and what I need is for the prior year to be an average of the entire year that is repeated by week. 

So for example--The matrix on the right has the measure indicated in red. But, it's being averaged out by each week. The annual average (annual visits/weeks in year) should be about 2900 and I just need that value repeated for each week. Hope that makes sense. I've tried working with the ALL function, but it's not working for me. I need the measure to change the year it is measuring depending on what is selected in the slicer, but I want the average to be independent of the particular week in the year and just be the annual average by the number of weeks in the year. See my measures below.

 For additional clarification, here is my model:

Measures:

(Pretty sure the first measure is the problem--don't know how to fix it.)

VisitsPriorYear = CALCULATE(COUNT('Fact'[ID]),'Fact'[Opportunity]="Completed Encounter",'Fact'[Productivity]="Productivity Encounter",ALL('Date Dim'[Year]),SAMEPERIODLASTYEAR('Date Dim'[Date]))

Weeks Last Year = CALCULATE(MAXX('Date Dim','Date Dim'[WeekofYear]),SAMEPERIODLASTYEAR('Date Dim'[Date]))

AVG Weekly Visits Last Yr = DIVIDE([VisitsPriorYear],[Weeks Last Year])

Let me know if this is unclear or what additional clarification I can provide.

Thanks!

[Anonymous · Accepted solution]

// Date Dim must contain all days
// throughout all years present in
// your model. In a word, it must 
// be a proper Date table marked as such.
// And it does not matter if you use or
// not individual dates. They have to
// be there. If you don't want the
// users to use them, simply hide them.

// This measure returns the average of
// values over all the weeks of the
// previous year. The value displayed
// will be the same for whatever unit
// of time you're going to have on your
// rows or columns.

Avg Visits Per Week LY =
var __visibleYear = SELECTEDVALUE( 'Date Dim'[Year] )
var __result =
	CALCULATE(
		AVERAGEX(
			VALUES( 'Date Dim'[WeekID] ),
			CALCULATE (
			    COUNT ( 'Fact'[ID] ),
			    KEEPFILTERS(
			    	'Fact'[Opportunity] = "Completed Encounter"
			    ),
			    KEEPFILTERS(
			    	'Fact'[Productivity] = "Productivity Encounter"
			    )
			)
		),
		ALL ( 'Date Dim' ),
		'Date Dim'[Year] = __visibleYear - 1
	)
RETURN
	__return
	
// If you slice your fact table in the GUI by
// any columns in the fact table, you're doing
// it incorrectly. Slicing and dicing must be
// done always only through dimensions if you don't
// want to have problems and write correct DAX.
// All columns in fact tables must/should be hidden.
// Only measures defined in there can be exposed.

// Therefore, you should have 2 more dimensions:
// Opportunity and Productivity. Then, the measure
// will be:

// If ID in the fact table is a real ID of the record,
// then it should be removed and the base measure
// should use COUNTROWS. In big fact tables there should
// not be fields that identify rows because such columns
// are not needed and the only effect they have is
// they baloon the memory needed to hold the data.
[Base Measure] = COUNTROWS( 'Fact' )

Avg Visits Per Week LY =
var __visibleYear = SELECTEDVALUE( 'Date Dim'[Year] )
var __result =
	CALCULATE(
		AVERAGEX(
			VALUES( 'Date Dim'[WeekID] ),
			// The below expression should be a measure itself.
			// CALCULATE ( COUNT ( 'Fact'[ID] ) )
			[Base Measure]
		),
	    KEEPFILTERS(
	    	Opportunity[Opportunity] = "Completed Encounter"
	    ),
	    KEEPFILTERS(
	    	Productivity[Productivity] = "Productivity Encounter"
	    ),
		'Date Dim'[Year] = __visibleYear - 1,
		ALL ( 'Date Dim' )
	)
RETURN
	__return

Best

D

[StephenK]

Thanks @Anonymous! Your solution worked perfectly. I appreciate the help.