当相关日期没有数据(过去和未来)时，如何使用相同的度量进行计算? [英] How to calculate using the same measure when related date has no data (past and future)?
问题描述
在尝试了很多方法和公式后，我决定问这个问题.
看到这个矩阵视觉:
WeekDate 是名为 Planning 的表中的列.它与另一个名为 Export 的表中的另一个日期时间列 Week_Imported 相关.
Export 表仅包含以下日期的值:23-Dec-19、30-Dec-19 和 06-Jan-20，而 Planningcode> 表的日期跨越过去和未来的多个星期.
Planning 和 Export 之间存在匹配的日期，Cumulative Plan Count 计算正确.
现在，即使没有日期匹配，我也想继续计算.我想从具有数据的 FIRSTDATE 中获取值 32 (在本例中为 2019 年 12 月 23 日)并用 32 回填过去.
对于未来的日期，我想使用 LASTDATE (06-Jan-20) 值，即 89.
类似这样的:
WeekDate 累积计划计数...19 年 11 月 25 日 3219 年 12 月 2 日 32 日19 年 12 月 9 日 32 日19 年 12 月 16 日 3219 年 12 月 23 日 32 <= 有数据的第一个 WeekDate [用 32 回填过去]19 年 12 月 30 日 5706-Jan-19 89 <= Last WeekDate 有数据[用 89 填充未来]20 年 1 月 13 日 8920-1-20 8920 年 1 月 27 日 89...用于累积SUM的公式是这样的:
累积计划计数 =计算 (ROUNDUP([1 - 目标] * MAX(计划[1 - 计划]), 0),筛选 (全部(计划[WeekDate])，计划[WeekDate] <= MAX(Planning[WeekDate])))####### 编辑 1 #######
使用下面的这个度量，我过去得到 1s...
1 - 目标 =VAR minWeek = MIN(Export[Week_Imported])VAR 目标计数 =计算 (COUNT('出口'[1 - 计划]),FILTER('Export', OR(Export[1 - Plan]="YES", Export[1 - Plan]="_")))var minTarget = 计算 (COUNT('出口'[1 - 计划]),FILTER('Export', OR(Export[1 - Plan]="YES", Export[1 - Plan]="_")&&导出[Week_Imported] = minWeek))返回开关(真，targetCount = BLANK(), 1,//这是我需要获取第一行值 (32) 并填充上面的列的地方...目标计数)问题是，无论我做什么，我都无法获得 23-Dec-2019 (32) 的第一个值来填充 Cumulative Plan Count 列.
这是我使用上面公式时的结果:
WeekDate 累积计划计数...19 年 11 月 25 日 12019 年 12 月 2 日 119 年 12 月 9 日 119 年 12 月 16 日 123-Dec-19 32 <= First WeekDate 有数据19 年 12 月 30 日 5706-Jan-19 89 <= Last WeekDate 有数据20 年 1 月 13 日 8920-1-20 8920 年 1 月 27 日 89...####### 编辑 2 #######
<块引用>我整理了一个简化的 Sample.pbix，它显示了我正在尝试的内容用最少的数据完成测试:
以这个度量为起点，我们需要将计算扩展到数据不存在的时期.为此，我们需要处理评估 [Plan Count] 的过滤器上下文.
我们需要获取 Export 数据存在的第一周和最后几周.这是一个无论切片器如何都返回数据的第一个日期的公式.
第一个数据周 =计算 (MIN(计划[WeekDate])，REMOVEFILTERS ( Planning[WeekDate] ),条约 (可计算(VALUES ( 'Export'[Week_Imported] ), -- 外键引用 Planning[WeekDate]去除过滤器(规划)),计划[周日期]))我们可以使用这个日期来修改过滤上下文来计算第一个数据周的[Plan Count].实际上，下面的 measure 总是返回 1064，即 2019 年 12 月 30 日 [Plan Count] 的数量.
第一周计划计数 =VAR _FirstDataWeek = 计算 (MIN(计划[WeekDate])，REMOVEFILTERS ( Planning[WeekDate] ),条约 (可计算(值(出口"[Week_Imported])，去除过滤器(规划)),计划[周日期]))返回计算 ([计划计数]，计划[WeekDate] = _FirstDataWeek)使用这种技术，我们可以将 [Plan Count] 的第一个和最终值扩展到过去和未来的日期.下面是最终的公式，它遍历Planning表，并应用不同的过滤上下文来计算[Plan Count].
扩展计划计数 =-- 存在导出数据的 Planning[WeekDate] 值VAR _DataWeeks = CALCULATETABLE (值(规划[WeekDate])，REMOVEFILTERS ( Planning[WeekDate] ),条约 (可计算(VALUES ( 'Export'[Week_Imported] ), -- 外键引用 Planning[WeekDate]去除过滤器(规划)),计划[周日期]))-- 存在导出数据的第一个和最后一个 Planning[WeekDate]VAR _FirstDataWeek = MINX (_DataWeeks, [WeekDate])VAR _FinalDataWeek = MAXX (_DataWeeks, [WeekDate])-- [Plan Count] 第一周和最后一周的值VAR _FirstDataWeekPlanCount = CALCULATE([计划计数]，Planning[WeekDate] = _FirstDataWeek)VAR _FinalDataWeekPlanCount = CALCULATE([计划计数]，Planning[WeekDate] = _FinalDataWeek)返回总和(规划，转变 (真的，计划[周日期] <_FirstDataWeek，_FirstDataWeekPlanCount，计划[周日期] >_FinalDataWeek，_FinalDataWeekPlanCount，[计划计数]))After trying a lot of approaches and formulas I decided to ask this question.
See this Matrix visual:
WeekDate is column in a table called Planning. It's related to another date time column Week_Imported in another table called Export.
Export table only has values for the dates: 23-Dec-19, 30-Dec-19 and 06-Jan-20 whereas Planning table has dates spanning multiple weeks in the past and future.
Cumulative Plan Count is calculating correctly as long as there are matching dates between the tables Planning and Export.
Now I'd like to keep calculating even when there's no date matching. I want to get the value 32 from the FIRSTDATE which has data (in this case 23-Dec-2019) and backfill the past with 32.
For dates in the future I'd like to use LASTDATE (06-Jan-20) value which is 89.
Something like this:
WeekDate Cumulative Plan Count
.
.
.
25-Nov-19 32
02-Dec-19 32
09-Dec-19 32
16-Dec-19 32
23-Dec-19 32 <= First WeekDate which has data [backfill past with 32]
30-Dec-19 57
06-Jan-19 89 <= Last WeekDate which has data [fill future with 89]
13-Jan-20 89
20-Jan-20 89
27-Jan-20 89
.
.
.
The formula used for the cumulative SUM is this:
Cumulative Plan Count =
CALCULATE (
ROUNDUP([1 - Target] * MAX(Planning[1 - Plan]), 0)
,
FILTER (
ALL ( Planning[WeekDate] ),
Planning[WeekDate] <= MAX(Planning[WeekDate])
)
)
####### Edit 1 #######
Using this measure below I get 1s for the past...
1 - Target =
VAR minWeek = MIN(Export[Week_Imported])
VAR targetCount =
CALCULATE (
COUNT( 'Export'[1 - Plan]),
FILTER('Export', OR(Export[1 - Plan]="YES", Export[1 - Plan]="_")))
var minTarget = CALCULATE (
COUNT( 'Export'[1 - Plan]),
FILTER('Export', OR(Export[1 - Plan]="YES", Export[1 - Plan]="_")
&& Export[Week_Imported] = minWeek))
RETURN
SWITCH(TRUE,
targetCount = BLANK(), 1, // Here is where I need to get the 1st row value (32) and fill up the column up above...
targetCount)
The problem is that no matter what I do I can't get the 1st value for 23-Dec-2019 (32) to fill up the Cumulative Plan Count column.
This is the result when I use the formula above:
WeekDate Cumulative Plan Count
.
.
.
25-Nov-19 1
02-Dec-19 1
09-Dec-19 1
16-Dec-19 1
23-Dec-19 32 <= First WeekDate which has data
30-Dec-19 57
06-Jan-19 89 <= Last WeekDate which has data
13-Jan-20 89
20-Jan-20 89
27-Jan-20 89
.
.
.
####### Edit 2 #######
I put together a simplified Sample.pbix which shows what I'm trying to accomplish with minimum data to test things: https://drive.google.com/drive/folders/1zxS_2VE9_0JEMXvsg9Dq196BK552RbNo?usp=sharing
This screenshot has more details: https://drive.google.com/open?id=1_-IMEpLwuWWN6vrrT_TNWbeqZ7f1LOan
Let me introduce the solution with intermediate steps.
In your data schema, Planning and Export tables are in one to many relationship. Planning is in the grain of every week, while Export has more rows for each week.
On this basis, the measure to count the number of Export rows for each Planning week is as simple as this.
Plan Count (Basic) = COUNTROWS ( 'Export' )
When you slice by Planning[WeekDate], this measure returns the counts of Export rows for the corresponding weeks.
Actually, you need FILTER to count only rows you are interested in.
Plan Count =
COUNTROWS (
FILTER (
'Export',
OR ( 'Export'[Plan] = "YES", 'Export'[Plan] = "_" )
)
)
Here is the result we get so far.
Having this measure as the starting point, we need to extend the calculation to the periods where the data does not exist. In order to do that, we need to handle the filter context where [Plan Count] is evaluated.
We need to get the first and final weeks where Export data exists. Here is a formula that returns the first date of the data regardless of the slicer.
First Data Week =
CALCULATE (
MIN ( Planning[WeekDate] ),
REMOVEFILTERS ( Planning[WeekDate] ),
TREATAS (
CALCULATETABLE (
VALUES ( 'Export'[Week_Imported] ), -- Foreign key referencing Planning[WeekDate]
REMOVEFILTERS ( Planning )
),
Planning[WeekDate]
)
)
We can use this date to modify the filter context to calculate [Plan Count] of the first data week. In fact, below measure always returns 1064, which is the number of [Plan Count] in December 30, 2019.
First Week Plan Count =
VAR _FirstDataWeek = CALCULATE (
MIN ( Planning[WeekDate] ),
REMOVEFILTERS ( Planning[WeekDate] ),
TREATAS (
CALCULATETABLE (
VALUES ( 'Export'[Week_Imported] ),
REMOVEFILTERS ( Planning )
),
Planning[WeekDate]
)
)
RETURN
CALCULATE (
[Plan Count],
Planning[WeekDate] = _FirstDataWeek
)
Using this technique, we can expand the first and final values of [Plan Count] to the past and the future dates. Below is the final formula, which iterates over Planning table, and apply different filter context to calculate [Plan Count].
Extended Plan Count =
-- Planning[WeekDate] values where Export data exists
VAR _DataWeeks = CALCULATETABLE (
VALUES ( Planning[WeekDate] ),
REMOVEFILTERS ( Planning[WeekDate] ),
TREATAS (
CALCULATETABLE (
VALUES ( 'Export'[Week_Imported] ), -- Foreign key referencing Planning[WeekDate]
REMOVEFILTERS ( Planning )
),
Planning[WeekDate]
)
)
-- First and last Planning[WeekDate] where Export data exists
VAR _FirstDataWeek = MINX ( _DataWeeks, [WeekDate] )
VAR _FinalDataWeek = MAXX ( _DataWeeks, [WeekDate] )
-- [Plan Count] values of first and last weeks
VAR _FirstDataWeekPlanCount = CALCULATE ( [Plan Count], Planning[WeekDate] = _FirstDataWeek )
VAR _FinalDataWeekPlanCount = CALCULATE ( [Plan Count], Planning[WeekDate] = _FinalDataWeek )
RETURN
SUMX (
Planning,
SWITCH (
TRUE,
Planning[WeekDate] < _FirstDataWeek, _FirstDataWeekPlanCount,
Planning[WeekDate] > _FinalDataWeek, _FinalDataWeekPlanCount,
[Plan Count]
)
)
这篇关于当相关日期没有数据(过去和未来)时，如何使用相同的度量进行计算?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
