DAX年初至今平均值

本文介绍了DAX年初至今平均值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

需要计算DAX的YTD平均和LYTD平均，我们的会计年度始于4月因此，如果我当前的日期是2017年6月5日，那么lytd_AVG从4月到6月将是（MTD 4月17日）+（MTD 5月17日）+（MTD 6月17日）/ 3

和LYTD_AVG将为（MTD 4月16日）+（MTD 5月16日）+（MTD 6月16日）/ 3

假设我当前的日期是2017年3月6日，那么年初至今的平均值为60

解决方案

假设给出了您的数据每月一次（因为您没有示例数据），对于年初至今，您应该可以使用如下所示的内容：

年初至今= CALCULATE（AVERAGE（Table [Value]），DATESYTD（Table [Date]， 03-31））

最后一个参数指定结束日期（

Need to calculate YTD Average and LYTD Average in DAX , Our Fiscal year starts from Apr. So if my current day is 5th of the June 2017 then lytd_AVG would be from Apr to June would be (MTD Aprn 17) + (MTD May 17) + (MTD June 17)/3

and LYTD_AVG would be (MTD Apr 16) + (MTD May 16) + (MTD June 16)/3

Assuming my current date is 06-03-2017, the YTD avg would be 60

解决方案

Assuming your data is given monthly (since you have no sample data), for YTD you should be able to use something that looks like this:

YTD Avg = CALCULATE(AVERAGE(Table[Value]), DATESYTD(Table[Date], "03-31"))

The last argument specifies the year-end date (Reference).

You can use the SAMEPERIOODLASTYEAR function for LYTD:

LYTD Avg = CALCULATE(AVERAGE(Table[Value]), SAMEPERIODLASTYEAR(DATESYTD(Table[Date], "03-31")))

Edit: Since your data is daily, we need to do a bit more work.

First, add month and year calculated columns to your table:

Month = MONTH(Table[Date]) FiscalYear = YEAR(MINX(DATESYTD(Table[Date], "03-31"), [Date]))

Then you will group by months in your measures:

YTD = AVERAGEX SUMMARIZE(Table1, Table1[Month], "MTDAmount", SUM(Table1[Amount])), [MTDAmount])

LYTD = AVERAGEX( SUMMARIZE( FILTER(ALL(Table1), Table1[Month] IN VALUES(Table1[Month]) && Table1[FiscalYear] = MAX(Table1[FiscalYear]) - 1), Table1[Month], "MTDAmount", SUM(Table1[Amount])), [MTDAmount])

You should then be able to build tables like this: