Total average of last 12 months

[ariana_night]

I've been trying to get the right measure for what I am looking for with no luck.

When I bring in Report Date and Sum of Gross Dollars, I am simply trying to get the total average ($4,346,951) into a card visual, like I see in Excel when I export the data:

I've tried this DAX but get a total of $4,358,088 (close but no match)

12mthavginv =
VAR CurrentDate = MAX('Inventory Provision'[ReportDate])
VAR StartDate = EOMONTH(CurrentDate, -12) + 1
VAR mthstart = MONTH(StartDate)
var result = CALCULATE(
    SUM('Inventory Provision'[GrossDollars]), REMOVEFILTERS('Calendar'),
    'Calendar'[Date]>= StartDate, 'Calendar'[Date] <= CurrentDate)
RETURN
DIVIDE(result,12)

The goal is to get the total of the last 12 months for each report date i.e. if I filter the report to May 2024, I should see the 12 month average for the last 12 months from that current date. If I filter to June 2024, then last 12 months from that current date and so on and so forth. 

My report is filtered to May 2024, and I should see $4,346,951 in the below visual where it is highlighted.

[MalkhazTsagarel]

Hello, 
This code is dynamic, so if the last 12 months are not in our data,it will use minimum date in our data.
for example if we select  01.09.2023(september 1st), we wont have all 12 months before in our data,
so it will calculate average of last available data before 01.09.2023:
AVG=

VAR CurrentDate = MAX('Inventory Provision'[ReportDate])

VAR StartDate = IF(EOMONTH(CurrentDate, -12) + 1<MINX(ALL('Calendar'[reporting date]),'Calendar'[reporting date]),MINX(ALL('Calendar'[reporting date]),'Calendar'[reporting date]),EOMONTH(CurrentDate, -12) + 1)

VAR mthstart = MONTH(StartDate)

var result = CALCULATE(

    SUM('Inventory Provision'[GrossDollars]), REMOVEFILTERS('Calendar'),

    'Calendar'[Date]>= StartDate, 'Calendar'[Date] <= CurrentDate)

RETURN

DIVIDE(result,

COUNTROWS(DISTINCT(SELECTCOLUMNS(

        CALENDAR(StartDate, CurrentDate),

        "MonthNumber", MONTH([Date])))
This is calendar calculation:

Calendar = addcolumns(calendar(MIN('Inventory Provision'[ReportDate]),MAX('Inventory Provision'[ReportDate])),"reporting date",eomonth([Date],-1)+1).
@ariana_night  If it works please make it as solution

[DataNinja777]

Hi @ariana_night 

I just copied and pasted your dax formula in Power BI and it was producing your required output as shown below:

I attach the pbix file with the data model.