Sum row with next row value and grab next value in

问题:

Let's say I have this data:

         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 

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

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

30-Apr  13,173    50,699    

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

回答1:

I'll call your table "Data".

Create a measure:

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
    )

Create another measure:

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 )

Result:

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

回答2:

It will help to have access to date intelligence, so create a date table if you don't have one already. The following dax sets up a small table that just barely covers the sample data in your example.

Date = CALENDAR(Date(2019,4,10),Date(2019,5,5))

Create a relationship between the Dates in your table and the new Date dimension. Add a slicer to your visuals using the Date dimension.

We can use IF and ISFILTERED to check if filtering is being done. If we aren't filtering on Date then we get the normal table behavior. If we are, we'll see our modified result.

Earn _ Alt = 
var tomorrow = CALCULATE (
    SUM(Table1[Earn]),
    dateadd('Table1'[Date], 1, DAY)
 )

return Sum(Table1[Earn]) + IF(ISFILTERED`('Date'[Date]),tomorrow,0)`

and

Earn Cum. _ Alt = IF(ISFILTERED('Date'[Date]),
CALCULATE (
    SUM(Table1[Earn Cum.]),
    dateadd('Table1'[Date], 1, DAY)
 ),
SUM(Table1[Earn Cum.]))

Results:

-Unfiltered-

-Filtered-