Rolling Semester (Half Year)

[Anonymous]

Hey All,

I need to create a measure to achieve a rolling semester rate based off the previous two quarters. I've used PARALLELPERIOD but that can only yield results by quarter. Below is an example of my data - I have the actual rate and the measure I need is the applied rate which would be the average from the previous semester. Any help would be tremendously appreciated!

[Anonymous]

can you add that data so I can grab it?  Don't feel like typing it

[Anonymous]

@Anonymous let me know if this works for you, thanks for giving it a look!

Month	     Fiscal Quarter Year	Actual Rate	Applied Rate
4/1/2018	Q3 2018	18%	
5/1/2018	Q3 2018	15%	
6/1/2018	Q3 2018	12%	
7/1/2018	Q4 2018	22%	
8/1/2018	Q4 2018	9%	
9/1/2018	Q4 2018	18%	
10/1/2018	Q1 2019		15.67%
11/1/2018	Q1 2019		15.67%
12/1/2018	Q1 2019		15.67%
1/1/2019	Q2 2019		15.67%
2/1/2019	Q2 2019		15.67%
3/1/2019	Q2 2019		15.67%
4/1/2019	Q3 2019		
5/1/2019	Q3 2019		
6/1/2019	Q3 2019		

[Anonymous]

It does.  I'm just not entirely sure what you are after.  How'd you get that 15.67% number?  

[Anonymous]

@Anonymous it's the average of the previous semester which is the measure I'm trying to create

[Anonymous · Accepted solution]

I attached the PBIX file below because had to do some work in Power Query to get the data set up since there is no dedicated Calendar table.  Easier to look at the file instead of me listing out all the steps. 

The end result is:

That Index column above is what I was going after.  That Calculated Column is:

Index = 
VAR CurrentDate= 'RollingSemester'[Date]
RETURN

CALCULATE(
    DISTINCTCOUNT('RollingSemester'[FySemID]),
        FILTER(
            ALL( 'RollingSemester'),
                CurrentDate >= RollingSemester[Date]
        ) 
    )

With that Index, I can set the min and max of the previous semester:

Min of Prev Index = 
CALCULATE(
    MIN('RollingSemester'[Date]),
    FILTER( 
        ALL( 'RollingSemester'),
        RollingSemester[Index] = MAX( RollingSemester[Index]) -1
    )
)

Max of Prev Index = 
CALCULATE(
    MAX('RollingSemester'[Date]),
    FILTER( 
        ALL( 'RollingSemester'),
        RollingSemester[Index] = MAX( RollingSemester[Index]) -1
    )
)

Now that we have the min and max of what we want to average, can use that a few ways.  I decided to use DATESBETWEEN:

Average of Actual Rate = 
	AVERAGE(RollingSemester[Actual Rate])

Applied Rate = 
IF( MAX('RollingSemester'[Index]) <> 1,
	CALCULATE( 
		[Average of Actual Rate],
		DATESBETWEEN(
			'RollingSemester'[Date],
			[Min of Prev Index],
			[Max of Prev Index]
		)
	)
)

Then if you want to show the actual rate if there, or the average if not in one column can use:

Actual and Applied one Measure = 
IF ( ISBLANK([Average of Actual Rate]),[Applied Rate],[Average of Actual Rate])

Might not be 100% what you were looking for, but should hopefully be a good starting point

Pbix File, Rolling Semester

[Anonymous]

@Anonymous thanks a ton man, this is perfect! 

I had tried using the datesinperiod function based off a semester start date, but creating a pairing end date and using datesbetween works much better. Really appreciate the help.

[Anonymous]

Glad it helped!