Total average of last 12 months
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.
