如果有基于剪切日期的数据，则操作最后两行 [英] Manipulating last two rows if there's data based on a Cut date

问题描述

这个问题是

P6 更新 表中的日期基本上被用作截止日期，并将固定静态.它是从 Excel 工作表中导入的，用户可以根据需要对其进行自定义.

当在测试数据表中为 P6 更新 日期找到匹配行时，会发生以下情况:

• 列每日收入 - 必须将其值与下一行相加(如果有的话)；

• 列 Earned Cum - 必须获取下一行的值；

• 之前的所有行都应该保持原样，即它们的值不会改变；

• 所有后续行的值都必须为 0.

例如:

如果 P6 更新 是 1-May-2018，这是预期的结果:

1-5 月 7,498 52,1065 月 2 日 0 0

如果 P6 更新 是 30-Apr-2018，这是预期的结果:

30-Apr 13,173 50,6991-5月0 05 月 2 日 0 0

如果 P6 更新 是 29-Apr-2018，这是预期的结果:

29-Apr 11,906 44,6084月30日 0 01-5月0 05 月 2 日 0 0

等等……

希望这是有道理的.

这在 Excel 中更容易，但在 Power BI 中尝试这样做让我发疯了.

解决方案

我将忽略之前提出的相关问题，从头开始.

首先，创建一个度量:

当前收入 =计算 (SUM('测试数据'[值]),'测试数据'[Act Rem] = "实际单位",'测试数据'[类型] = "当前")

此度量将用于其他度量，以免您一次又一次地输入所有这些条件(实际单位"和当前").在其他度量中重用度量是一个很好的实践 - 节省工作，使代码更清晰，更容易重构.

创建另一个度量:

Cut Date = SELECTEDVALUE('P6 Update'[Date])

我们将在需要截止日期时使用此措施.请注意，它不必是硬编码的 - 如果 P6 表包含日期列表，您可以从日期创建下拉切片器，并且可以动态选择截止日期.该公式将正常工作.

创建第三个度量:

下一个赚取 =VAR Cut_Date = [切割日期]VAR Current_Date = MAX ('测试数据'[日期])VAR Next_Date = Current_Date + 1VAR Current_Earn = [当前收入]VAR Next_Earn = CALCULATE ([Current Earn], 'Test data'[Date] = Next_Date)返回转变 (真的，当前_日期 

我不确定Next Earn"是不是一个好名字，希望你能找到一个更直观的名字.它的工作方式:我们将所有必要的输入保存到变量中，然后使用 SWITCH 函数来定义结果.希望它是不言自明的.(注意:如果您需要在截止日期上方设置 0，请将 BLANK() 替换为 0).

最后，我们定义了累积收入的衡量标准.它不需要任何特殊的逻辑，因为之前的措施已经妥善处理了它:

Cum Earn =VAR Current_Date = MAX('测试数据'[日期])返回计算([下一个赚取]，FILTER(ALL('Test data'[Date]), 'Test 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 fixedstatic. 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屋！