我有两个表,第一个是数据的清理版本,第二个是我需要计算的。
AnimalCount
Animal DATE Count
Lion 2019-01-01 2
Cat 2019-01-01 45
Cat 2019-01-02 40
Fish 2019-01-02 900
Cat 2019-01-03 50
Fish 2019-01-05 800
Fish 2019-01-06 1200
Cat 2019-01-07 45
Lion 2019-01-07 1
Lion 2019-01-08 0
Calculated
DATE (unique) Sum of latest Count by Animal
2019-01-01 47 = 45 Cats + 2 Lions
2019-01-02 942 = 900 Fish + 40 Cats + 2 Lions
2019-01-03 952 = 50 Cats + 900 Fish + 2 Lions
2019-01-05 852 = 800 Fish + 50 Cats + 2 Lions
2019-01-06 1252 = 1200 Fish + 50 Cats + 2 Lions
2019-01-07 1246 = 1 Lion + 45 Cats + 1200 Fish
2019-01-08 1245 = 45 Cats + 1200 Fish以下是我到目前为止尝试过的查询,它们给出的结果与我正在寻找的结果略有不同。
如果有什么建议,我会非常感激的。
Query 1.
LatestTotal = SUMX(
GROUPBY(
FILTER(
AnimalCount,
[DATE] <= EARLIER([DATE])
),
[Animal],
"LatestGroupTotal",
SUMX(CURRENTGROUP(), [Count])
),
[LatestGroupTotal]
)
Query 2.
LatestTotal = SUMX(
ADDCOLUMNS(
ADDCOLUMNS(
VALUES(AnimalCount[Animal]),
"AnimalName",
[Animal],
"LastDate",
CALCULATE(
MAX(AnimalCount[DATE]),
NOT(ISBLANK(AnimalCount[Count]))
)
),
"LastValue",
SUMX(
FILTER(
AnimalCount,
[AnimalName] = [Animal]
&& [DATE] = [LastDate]
),
[Count]
)
),
[LastValue]
)发布于 2019-06-07 03:06:31
首先,为动物总数创建一个度量值(为了进一步方便):
Total Animal Count = SUM(AnimalCount[Count])然后,添加此度量:
Latest Total =
VAR
Current_Date = MAX ( AnimalCount[DATE] )
VAR
All_Latest_Dates =
ADDCOLUMNS (
ALL ( AnimalCount[Animal] ),
"Last Date", CALCULATE (
MAX ( AnimalCount[DATE] ),
AnimalCount[DATE] <= Current_Date )
)
VAR Animal_Latest_Dates =
TREATAS ( All_Latest_Dates, AnimalCount[Animal], AnimalCount[DATE] )
RETURN
CALCULATE ( [Total Animal Count], Animal_Latest_Dates )结果:

解释:
此公式要求理解DAX中的“数据沿袭”概念:
Understanding Data Lineage in DAX
简而言之:
在variable;
这种方法会产生一个最佳的查询执行计划,即使您的数据量很大,该公式也应该会给您带来良好的性能。
发布于 2019-06-07 03:12:19
这个怎么样?
LatestTotal =
VAR CurrDate =
MAX ( AnimalCount[DATE] )
VAR Summary =
SUMMARIZE (
FILTER ( ALLSELECTED ( AnimalCount ), AnimalCount[DATE] <= CurrDate ),
AnimalCount[Animal],
"LastDate", MAX ( AnimalCount[DATE] )
)
VAR CountAtDate =
ADDCOLUMNS (
Summary,
"Count", LOOKUPVALUE (
AnimalCount[Count],
AnimalCount[Animal], [Animal],
AnimalCount[DATE], [LastDate]
)
)
RETURN
SUMX ( CountAtDate, [Count] )首先,我们通过Animal对表进行总结,查找与每个日期相关联的早于或等于我们所在行的日期的最后日期(CurrDate)。
然后我们查找该动物在那一天的计数,并将所有这些计数加在一起。
https://stackoverflow.com/questions/56482188
复制相似问题