powerbi - Dax 函数 DATESINPERIOD 未给出正确答案
问题描述
我需要找到搬家两天的销售额。我正在使用 DAX 函数 DatesinPeriod 但输出不正确。请帮助我了解我哪里出错了。我正在使用以下 Dax 公式:
Measure = CALCULATE(sum('Table'[Sale]),DATESINPERIOD('Dim Date'[Date],SELECTEDVALUE('Table'[Date]),-2,day))
要复制场景,第一步是使用 -> 创建Dim Date表
Dim Date = GENERATESERIES(date(2019,01,01),date(2019,12,31),1)
第二步是创建 DataTable ->
Table = DATATABLE("Date",DATETIME,"Flag1",STRING,"Flag2",STRING,"Sale",INTEGER,{
{"8/1/2019","True","True",200},
{"8/2/2019","False","True",80},
{"8/2/2019","False","True",80},
{"8/2/2019","False","True",80},
{"8/2/2019","False","True",80},
{"8/2/2019","False","True",80},
{"9/3/2019","False","True",60},
{"9/4/2019","False","True",10},
{"9/5/2019","False","True",100},
{"9/6/2019","False","True",30},
{"9/7/2019","False","True",60},
{"9/8/2019","False","False",150},
{"9/9/2019","False","False",80},
{"9/10/2019","False","False",90},
{"9/11/2019","False","False",30},
{"9/12/2019","False","False",20},
{"10/13/2019","False","True",50},
{"10/14/2019","False","True",60},
{"10/15/2019",BLANK(),BLANK(),BLANK()},
{"10/16/2019",BLANK(),BLANK(),BLANK()}
})
第三步 - 在日期列上创建这些表之间的关系
第 4 步 - 创建 Measure 使用 -Measure = CALCULATE(sum('Table'[Sale]),DATESINPERIOD('Dim Date'[Date],SELECTEDVALUE('Table'[Date]),-2,day))
你会看到输出错误。看截图。这很奇怪。我尝试使用 DatesBetween 函数,它也给了我同样的错误输出。
解决方案
使用以下度量来获得预期的结果:
SumInRange =
VAR __selectedDate = SELECTEDVALUE( 'Table'[Date] )
VAR __subTable =
FILTER(
ALL( 'Table'[Date] ),
AND(
'Table'[Date] >= __selectedDate -2,
'Table'[Date] <=__selectedDate
)
)
Return
CALCULATE(
SUMX (
DISTINCT ( 'Table'[Date] ),
CALCULATE ( MAX ( 'Table'[Sale] ) )
),
__subTable
)
推荐阅读
- xcode - 查看任何项目的任何情节提要时 xcode/simulator 崩溃
- excel - SaveAs PDF 宏在不同 Excel 版本上的奇怪行为
- javascript - React 姿势状态影响子组件姿势
- sql - 选择在两列 DBMS SQL 之间只有一种唯一关系的行
- c++ - _Unwind_RaiseException 示例
- azure - ARM 模板中“WindowsOSVersion”的哪个值?
- c# - 如何在字符串保存期间删除 & 字符
- c++ - 如何分离 `QObject` 的父子关系的关注点,例如与其他所有者互操作?
- marklogic - Marklogic如何使用序列化
- javascript - Adding regex for only Cyrillic letters and white spaces