DAX中计算YTD平均值与LYTD平均值的技术需求（财年始于4月）

Got it, let's break down how to compute these averages in DAX, since your fiscal year kicks off in April and you need the average of monthly MTD values for YTD and last year's YTD.

First, Define a Base MTD Measure #

Start with a simple measure to calculate the Month-to-Date value—this will be the building block for our averages:

MTD_Value = 
CALCULATE(
    SUM('YourFactTable'[YourValueColumn]), -- Replace with your actual value column
    DATESMTD('DateTable'[Date])
)

Calculate YTD Average (FY Starting April) #

This measure will compute the average of MTD values from the start of your fiscal year (April) up to the current date. For example, if today is June 5, 2017, it averages Apr 2017 MTD, May 2017 MTD, and Jun 2017 MTD.

YTD_AVG = 
VAR CurrentDate = MAX('DateTable'[Date])
VAR FiscalYearStart = DATE(YEAR(CurrentDate), 4, 1) -- April 1st of current year
-- Get all fiscal YTD months up to current date
var FiscalYTDMonths = 
    CALCULATETABLE(
        VALUES('DateTable'[MonthYear]), -- Use a column like "YYYY-MM" to group months
        DATESBETWEEN('DateTable'[Date], FiscalYearStart, CurrentDate)
    )
-- Average the MTD value for each of those months
RETURN
    AVERAGEX(FiscalYTDMonths, [MTD_Value])

Calculate LYTD Average (Last Year's Fiscal YTD) #

This is the same logic, but shifted back one year. For June 5, 2017, it averages Apr 2016 MTD, May 2016 MTD, and Jun 2016 MTD.

LYTD_AVG = 
VAR CurrentDate = MAX('DateTable'[Date])
VAR FiscalYearStart = DATE(YEAR(CurrentDate), 4, 1)
-- Shift dates back one year
var LastYearFiscalStart = DATE(YEAR(CurrentDate)-1, 4, 1)
var LastYearCurrentDate = DATE(YEAR(CurrentDate)-1, MONTH(CurrentDate), DAY(CurrentDate))
-- Get last year's fiscal YTD months
var LastYearFiscalYTDMonths = 
    CALCULATETABLE(
        VALUES('DateTable'[MonthYear]),
        DATESBETWEEN('DateTable'[Date], LastYearFiscalStart, LastYearCurrentDate)
    )
-- Average the MTD values for those months
RETURN
    AVERAGEX(LastYearFiscalYTDMonths, [MTD_Value])

Key Notes #

• Make sure your DateTable is complete (no missing dates) and properly related to your fact table.
• The MonthYear column can be created in your date table with a formula like FORMAT('DateTable'[Date], "YYYY-MM") to group dates by month/year.
• If some months have no values and you want to exclude them from the average, add a filter to FiscalYTDMonths like FILTER(VALUES(...), [MTD_Value] <> BLANK()).

As you mentioned, if the current date is June 3, 2017, this setup will return 60 for YTD_AVG assuming your underlying MTD values add up correctly.

内容的提问来源于stack exchange，提问作者Joseph