How to calculate average based on month selected from different Years

[Anonymous]

I have 2 table dates and Transactions

date table has data (all days and months)

Year	MOnth	date	ReportYear
2018	jan	01/01/2018	Previous year
2018	jan	02/01/2018	Previous year
2018	feb		Previous Year
2019	Jan		Current Year
2019	feb		Current Year

Now I have transaction Table

Year	Stream	Hour
2018	a	10
2018	a	20
2019	a	30
2019	a	40

Both the table are joined on Year column

Now i have 2 slicer 

• Month
• Report Year

and a table which shows

Stream	total Hours
a	Previous year hours = 30.  Selected months = Jan total hour= Monthly average  = 30/12 x 1 When multiple FYs is selected:  current Year hour= 30  Previous year = 70 Selected months = Jan 2018+Jan 2019+Feb 2019 = 3 months Total hours = (30/12) x 1 + (70/12) x 2

[rajendraongole1]

Hi @Anonymous - Please try to create the below measures: if the issue is not closed.

reference link:

Solved: calculate average based on academic year - Microsoft Fabric Community

Try below measures:

// Measure for Total Hours in the Current Year
TotalHoursCurrentYear =
CALCULATE(
SUM(Transactions[Hour]),
FILTER('Date', 'Date'[ReportYear] = "Current Year")
)

// Measure for Total Hours in the Previous Year
TotalHoursPreviousYear =
CALCULATE(
SUM(Transactions[Hour]),
FILTER('Date', 'Date'[ReportYear] = "Previous Year")
)

// Measure for Monthly Average Hours in the Current Year
MonthlyAverageHoursCurrentYear =
DIVIDE([TotalHoursCurrentYear], 12)

// Measure for Monthly Average Hours in the Previous Year
MonthlyAverageHoursPreviousYear =
DIVIDE([TotalHoursPreviousYear], 12)

// Measure for Selected Months
SelectedMonths =
COUNTROWS(
VALUES('Date'[Month])
)

// Measure for Total Selected Hours
TotalSelectedHours =
DIVIDE([TotalHoursPreviousYear], 12) * [SelectedMonths] +
DIVIDE([TotalHoursCurrentYear], 12) * [SelectedMonths]

// Measure for Display in the Table
DisplayTotalHours =
SUMX(
VALUES(Transactions[Stream]),
[TotalSelectedHours]
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!