Average of Previous Fiscal Year

[Anonymous]

Hey Guys,

So i am stuck here calculating average of previous fiscal year for a measure.

So i have a column called "FY" which has values as "FY17","FY18".Also i have a measure which calculates average of 4 columns :

Average Index=(Average (COl1)+Average(Col2)+Average(Col3)+Average(Col4))/4

Now i need to caluclated "Average Index" for previous Fiscal Year .

Please help me out guys this thing is really getting up my nerves now.

[technolog]

Step 1: Create the Average Index Measure
First, create the measure for the "Average Index":

AverageIndex =
(Average(Table[Col1]) + Average(Table[Col2]) + Average(Table[Col3]) + Average(Table[Col4])) / 4
Step 2: Create a Measure for the Previous Fiscal Year
Next, create a measure to calculate the "Average Index" for the previous fiscal year. Here’s how you can achieve that:

Measure to Get Previous Fiscal Year
Assuming your fiscal year format is "FY17", "FY18", etc., you can create a measure to identify the previous fiscal year:

PreviousFiscalYear =
VAR CurrentFY = MAX(Table[FY])
VAR PrevFY = "FY" & VALUE(RIGHT(CurrentFY, 2)) - 1
RETURN
PrevFY
Step 3: Calculate the Average Index for the Previous Fiscal Year
Now, create a measure to calculate the "Average Index" for the previous fiscal year:

AverageIndexPreviousFY =
CALCULATE(
[AverageIndex],
Table[FY] = [PreviousFiscalYear]
)
Step 4: Put It All Together
Ensure the measures are working together. The AverageIndexPreviousFY measure should now give you the average index for the previous fiscal year based on the "Average Index" measure.