How do I show the value of the previous row in the current column? (and last/end balance of a date)

[BrandoCrozier]

I am reviewing financial data from multiple accounts and I am trying to put my calculated balances from each table in one table that has every date of the year. I have 2 issues:

1. Not every date has a transaction so I want to be able to show the value from the previous date in any date that would be blank. I am using 0 as a placeholder so that the formula doesn't error. (Red text in formula is what I am trying to change)
2. I also want to show the last/end balance of a given date, but only know how to use Max, Min, or Sum. So far I did MAX('Table'[Index]) as an accurate placeholder because using Max('Table'[Balance]) can be inaccurate if a payment was made at the end of the day. (Blue text in formula is what I am trying to change)

Formula I am trying to use to bring the balances into my "Balance_By_Date" Table:

WFMatch = IF(
	ISBLANK(
		LOOKUPVALUE(WFYearToDate[Date],WFYearToDate[Date],Balance_By_Date[Date])
	)
	, IF(
        Balance_By_Date[Date] = MIN(Balance_By_Date[Date]), SUM(Year_Opening_Balances[WF]) , 0)
	, CALCULATE(
        MAX(WFYearToDate[Index])
))

WFMatch = IF(

    ISBLANK(

        LOOKUPVALUE(WFYearToDate[Date],WFYearToDate[Date],Balance_By_Date[Date])

    )

    , IF(

Balance_By_Date[Date] = MIN(Balance_By_Date[Date]), SUM(Year_Opening_Balances[WF]) , 0)

    , CALCULATE(

MAX(WFYearToDate[Index])

))

Image 1 - Formula I am trying to use to bring the balances into my "Balance_By_Date" Table

Image 2 - "WFYearToDate" Table

Thank you!!!

[v-juanli-msft · Accepted solution]

Hi @BrandoCrozier 

Open Edit queries, merge queries

then expand values, fill down (before fill down ,sort ascending by "date" column)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.