DAX年初至今平均值 [英] YTD Average in 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:

这篇关于DAX年初至今平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！