Append to row above in Power BI

[CROforce]

Hello… Is there a way to do something like this in Power BI?

In Excel, I have a table called TD_COUNT which consist of 4 columns, | Date | Open | Close | Total |

What I would like to accomplish is on a row 2, column D formula like this:

=IF([@[Date Open]]="","", [@Open])

but then on the row 3, I need to append row 2 column 😧

=IF([@[Date Open]]="","", [@Open]+D2)

and so on....

btw, I have created query table in Power BI called "Date List" and I have same columns as in Excel

[dkay84_PowerBI]

In power query (query editor in Power BI), you can create a custom column that references values in specific cells (i.e. a row offset such as the cell above). To do this, you would first need to add an index column starting with 1, and then the syntax for referencing that index column as a row offset would be:

=if [Date Open] = "" then "" else "Table"[Open]{Index-1}

Here the "Index" value will be the row number (essentially) so by subtracting 1 you are telling the function to use the value in the [Open] column but from the row above.

You will need to adapt this approach for your specific goal.  It looks like you want a running total perhaps?  Not sure but hope this helps

[CROforce]

Thank you for reply however, I can't get it to work. 

BTW, I am fearly new to Power BI. What I am looking for is if date 9/30/2015 has 10 open, I would like to take that 10 and add to the row below + whatever number under Open for the row 10/1/2015. So in this case would be 20 and so on (row on 10/2/2015 should be 30 since row above was 20 plus another 10 under open on that particular date)...  

[CROforce]

Is there a way to do this in DAX?

[Anonymous]

Try something like this as a measure, based on http://www.daxpatterns.com/cumulative-total/:

Cumulative Open Tickets = 
CALCULATE (
    SUM (Table1[Open]) - SUM (Table1[Closed]),
    FILTER (
        ALL (Table1),
        Table1[Index] <= MAX (Table1[Index])
    )
)

[CROforce]

Thank you for the reply/ your time to help with this.. this formula makes sence logically however it's not working 😞

Might be good thing to note that inorder to get number of open (column) I am using formula:

Open = COUNTX(
		FILTER('observation', 'observation'[Date Opened]='Date List'[Date Open]),
		'observation'[Date Opened])

And similar thing for Closed... not sure if that would affect the way Cumulative Open Tickets column?

This is my resoult with the code you provided:

I will also check the link you mentioned in your post

[Anonymous · Accepted solution]

That consistent 87 is suspicious.  Did you by chance create 'Cumulative Open Tickets' as a Column rather than a Measure?

[CROforce]

You are GENIUS :-)... I missed to see that. After quick change, now it works. Thank you for your help. It's much appriciated.