Calculating average based on range of dates in another table

[Anonymous]

Hello,

I have issues with figuring out how to calculate average of values for a fiscal year based on two date columns that are in another table.

There are 3 tables total:

1.  Department Name, Date, Start Date, End Date.

Columns Start and End date are a range of a fiscal year. Each department has a different start and end dates of a fiscal year.

There is one row for every month available for each department.

Table 1 filters table 2, many to many relationship from Department to Department Name.

2.  Department Name, Date, Average of Values.

There is one row for every month available for each department.

3. Calendar table which filters table no. 2.

I wrote a following calculation but it obviously doesn't work with fiscal year dates being in a different table: 

CALCULATE(

    AVERAGE('Monthly value'[MonthAVG]), DATESBETWEEN('Calendar'[Date].[Date], MIN('Months with FY'[FYFrom]), MAX('Months with FY'[FYTo])))

I tried adding a column to table 1 to include average of monthly value from table 2, but I failed to do that with RELATED. I tried adding inactive relationships between two tables but can't find a working configuration with USERELATIONSHIP.

Basically I try to create a measure that will display average for an entire fiscal year for a given department if a date within that fiscal year range is selected on a slicer. For example : Fiscal year is 05.2021 - 04.2022; if a user selects any month in that range a measure will display an average for an entire year.

[amitchandak]

@Anonymous , I think this can help

Average of Rolling, Average of Snapshots: https://youtu.be/_pZRdLAJxxA

[Anonymous]

Hi, thanks for that. Unfortunately I was not able to find a working way of connecting those two tables together. I understand it is a matter of creating a correct inactive relationship between dates columns. I tried including userelationships in formula but to no avail. I am not able to find a proper way to have this calculation include a part that would allow for it to get filtered by my calendar.

I attach a screenshot of a model. I tried playing with inactive relationships beween calendar date and FYFrom, FYTo, MM-YYYY