如果有基于截止日期的数据，则处理最后两行 [英] Manipulating last two rows if there's data based on a Cut date
问题描述
这个问题是
基本上， P6更新表中的日期用作 截止日期 ，并且固定不变。它是从Excel表格中导入的，用户可以在其中按自己的喜好对其进行自定义。
这是在 P6更新日期：
- 列
每日获利-必须将其值与下一行相加； -
列
收入总额-必须获取下一行行的值； -
所有先前的行应保持不变，也就是说，它们的值不会改变；
-
所有后续行的值都必须分配为0。
例如：
如果 P6更新是 2018年5月1日，则这是预期的结果：
1-五月7,498 52,106
2-五月0 0
如果 P6更新是 2018年4月30日，这是预期的结果：
30-Apr 13,173 50 ，699
1月5日0 0
2月5月0 0
如果 P6更新是 2018年4月29日，这是预期的结果：
4月29日11,906 44,608
4月30日0 0
1月5日0 0
2月5月2日0
等等...
希望这很有意义。
在Excel中这更容易，但是在Power BI中尝试这样做会使我发疯。
我将忽略先前询问的相关问题，并从头开始。
首先，创建一个度量：
当前收入=
CALCULATE（
SUM（'Test data'[Value]），
'Test data'[Act Rem] =实际单位，
'Test data'[Type] =当前
）
此度量将用于其他度量，以免输入所有这些条件（实际单位和当前）一次又一次地出现。在其他措施中重复使用措施是一个好习惯-节省工作，使代码更整洁，重构也更容易。
创建其他度量：
剪切日期= SELECTEDVALUE（'P6更新'[日期]）
我们将在需要截止日期时使用此度量。请注意，它不必进行硬编码-如果P6表包含日期列表，则可以从日期中创建下拉切片器，并可以动态选择截止日期。该公式将正常工作。
创建第三个度量：
下一个收入=
VAR Cut_Date = [剪切日期]
VAR Current_Date = MAX（'Test data'[Date]）
VAR Next_Date = Current_Date + 1
VAR Current_Earn = [当前收入]
VAR Next_Earn = CALCULATE（[当前收入]，'测试数据'[日期] = Next_Date）
返回
开关（
是，
Current_Date< Cut_Date，Current_Earn，
Current_Date = Cut_Date，Current_Earn + Next_Earn，
BLANK（）
）
我不确定 Next Earn是否是一个好名字，希望您会找到一个更直观的名字。工作方式：我们将所有必要的输入保存到变量中，然后使用SWITCH函数定义结果。希望它是不言自明的。 （注意：如果需要在截止日期之前为0，则将BLANK（）替换为0）。
最后，我们定义了累积收入的度量。它不需要任何特殊的逻辑，因为先前的度量可以正确地处理它：
总收入=
VAR Current_Date = MAX（'测试数据'[日期]）
返回
计算（
[下一个收入]，
FILTER（ALL（'测试数据'[日期]），'测试data'[Date]< = Current_Date））
结果：
This question is a slightly varied version of this one...
Now I'm using Measures instead of Calculated columns and the date is static instead of having it based on a dropdown list.
Here's the Power BI test .pbix file:
https://drive.google.com/open?id=1OG7keqhdvDUDYkFQFMHyxcpi9Zi6Pn3d
This printscreen describes what I'm trying to accomplish:
Basically the date in P6 Update table is used as a cut date and will be fixed\static. It's imported from an Excel sheet where the user can customize it however they want.
Here's what should happen when a matching row in Test data table is found for P6 Update date:
- column
Earned Daily- must have its value summed with the next row if there's one; column
Earned Cum- must grab the next row's value;all the previous rows should remain intact, that is, their values won't change;
all subsequent rows must have their values assigned 0.
So for example:
If P6 Update is 1-May-2018, this is the expected result:
1-May 7,498 52,106
2-May 0 0
If P6 Update is 30-Apr-2018, this is the expected result:
30-Apr 13,173 50,699
1-May 0 0
2-May 0 0
If P6 Update is 29-Apr-2018, this is the expected result:
29-Apr 11,906 44,608
30-Apr 0 0
1-May 0 0
2-May 0 0
and so on...
Hope this makes sense.
This is easier in Excel, but trying to do this in Power BI is making me go nuts.
I will ignore previously asked related questions and start from scratch.
First, create a measure:
Current Earn =
CALCULATE (
SUM( 'Test data'[Value]),
'Test data'[Act Rem] = "Actual Units",
'Test data'[Type] = "Current"
)
This measure will be used in other measures, to save you from typing all these conditions ("Actual Units" and "Current") again and again. It's a great practice to re-use measures in other measures - saves work, makes code cleaner and easier to refactor.
Create another measure:
Cut Date = SELECTEDVALUE('P6 Update'[Date])
We will use this measure whenever we need a cut off date. Please note that it does not have to be hard-coded - if P6 table contains a list of dates, you can create a pull-down slicer from the dates, and can choose the cut-off date dynamically. The formula will work properly.
Create third measure:
Next Earn =
VAR Cut_Date = [Cut Date]
VAR Current_Date = MAX ( 'Test data'[Date] )
VAR Next_Date = Current_Date + 1
VAR Current_Earn = [Current Earn]
VAR Next_Earn = CALCULATE ( [Current Earn], 'Test data'[Date] = Next_Date )
RETURN
SWITCH (
TRUE,
Current_Date < Cut_Date, Current_Earn,
Current_Date = Cut_Date, Current_Earn + Next_Earn,
BLANK ()
)
I am not sure if "Next Earn" is a good name for it, hopefully you will find a more intuitive name. The way it works: we save all necessary inputs into variables, and then use SWITCH function to define the results. Hopefully it's self-explanatory. (Note: if you need 0 above Cut Date, replace BLANK() with 0).
Finally, we define a measure for cumulative earn. It does not require any special logic, because previous measure takes care of it properly:
Cum Earn =
VAR Current_Date = MAX('Test data'[Date])
RETURN
CALCULATE(
[Next Earn],
FILTER(ALL('Test data'[Date]), 'Test data'[Date] <= Current_Date))
Result:
这篇关于如果有基于截止日期的数据，则处理最后两行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
