将行与下一行的值相加，然后在其他列中获取所选日期的下一个值 [英] Sum row with next row value and grab next value in other column for the date selected

问题描述

假设我有此数据:

         Earn     Earn Cum.
13-Apr       -        -   
14-Apr      48        48 
15-Apr     257       305 
16-Apr     518       823 
17-Apr     489     1,312 
18-Apr     837     2,149 
19-Apr   1,005     3,154 
20-Apr   1,021     4,175 
21-Apr   1,463     5,638 
22-Apr   2,630     8,268 
23-Apr   2,993    11,261 
24-Apr   3,354    14,615 
25-Apr   4,332    18,947 
26-Apr   4,885    23,832 
27-Apr   4,514    28,346 
28-Apr   4,356    32,702 
29-Apr   4,824    37,526 
30-Apr   7,082    44,608 
1-May    6,091    50,699 
2-May    1,407    52,106 

例如，当在下拉切片器中选择日期时:1-May我想对行1-May和下一行2-May求和 Earn 列的总和，并抓住收入总和列的下一个值52,106.结果应该是:

When a date is selected in a dropdown slicer for example: 1-May I'd like to sum the rows 1-May with the next row 2-May for the column Earn and grab the next value 52,106 for the column Earn Cum.. The result should be:

1-May    7,498    52,106

另一个示例:如果所选日期为4月30日，则结果必须为:

Another example: if the date selected was 30-Apr the result must be:

30-Apr  13,173    50,699    

我正在努力尝试使用Power BI中的一种方法来做到这一点.

I'm scratching my head trying to do this using a measure in Power BI.

推荐答案

我将您的表称为数据".

I'll call your table "Data".

创建度量:

Next Earn =
VAR Current_Date = MAX ( Data[Date] )
VAR Next_Date = Current_Date + 1
RETURN
    CALCULATE (
        SUM ( Data[Earn] ),
        Data[Date] = Current_Date || Data[Date] = Next_Date
    )

创建另一个度量:

Next Cum Earn = 
VAR Current_Date =  MAX ( Data[Date] )
VAR Next_Date = Current_Date + 1
RETURN
    CALCULATE ( SUM ( Data[Earn Cum] ), Data[Date] = Next_Date )

结果:

注意:该代码假定您的日期是连续的(没有间隔).如果它们之间存在差距，则情况会更加复杂.

Note: the code assumes that your dates are sequential (no gaps). If they have gaps, things are a bit more complex.

这篇关于将行与下一行的值相加，然后在其他列中获取所选日期的下一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！