将行与下一行值相加,并在其他列中获取所选日期的下一个值 [英] 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 的行与 Earn 列的下一行 2-May 相加并抓取Earn Cum. 列的下一个值 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屋!