Previous Day Balance - Excluding Weekends

[gmasta1129]

Hello,

I am trying to create a formula to pull in the previous day balance for a certain portfolio code. 

Please note, the report contains many portfolio codes, I am showing a sample of one code only below.  

This report runs Monday to Friday.  Therefore, I should see in Monday's "Previous Day Balance" column/cell, the balance as of Friday.  

Columns in my Table: 

Run Date

Portfolio Code

Today's Balance

Previous Day Balance (new column)

Can someone please help? 

	Run Date	Portfolio Code	Today's Balance	Previous Day Balance
Mon	10/13/2025	11111	50	35	*balance as of Friday 10/10/2025
Tue	10/14/2025	11111	70	50
Wed	10/15/2025	11111	65	70
Thurs	10/16/2025	11111	200	65
Frid	10/17/2025	11111	350	200
Mon	10/20/2025	11111	750	350
Tues	10/21/2025	11111	1000	750

[gmasta1129]

@GeraldGEmerick 

This worked perfectly. Thank you so much! 

[GeraldGEmerick]

Happy to help!

[jgeddes]

Here is a Power Query solution.

The basic idea is to group by Portfolio Code, remove weekend rows (if present), and then calculate the previous day balance. Any day that does not have a previous day returns null.

Consider the sample data...

You can achieve this result...

With the following code...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc/BCsIwDAbgd8l5sDS1rb6AN08Tdig7CA62i8K07+9qkw0yZy6l8JH8f4xweT6gAoO1sTUhufzJM78OoasiXFPP4qBFKKLt7yycFt6VHUOaXmy8NoRlzXkaZU/QxnKYJS7hJsyal0+R0cYgqj1rbcrzrb3bW0jA3eJCvKT51VwQ4Z/qgqwEam5vNsdNIL7WJKl10iRXn1H3AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Day = _t, #"Run Date" = _t, #"Portfolio Code" = _t, #"Today's Balance" = _t]),
    set_types = 
    Table.TransformColumnTypes(
        Source,
        {{"Day", type text}, {"Run Date", type date}, {"Portfolio Code", Int64.Type}, {"Today's Balance", Int64.Type}}
    ),
    group_rows = 
    Table.Group(
        set_types, 
        {"Portfolio Code"}, 
        {{"NestedTable", each _, type table [Day=nullable text, Run Date=nullable date, Portfolio Code=nullable number, #"Today's Balance"=nullable number]}}
    ),
    remove_weekend_days = 
    Table.TransformColumns(
        group_rows,
        {
            {
                "NestedTable", 
                each Table.Sort(
                        Table.SelectRows(
                        _, 
                        each not List.Contains({0,6}, Date.DayOfWeek([Run Date]))
                    ), 
                    {"Run Date", Order.Ascending}
                )
            }
        }
    ),
    transform_nested = 
    Table.TransformColumns( 
        remove_weekend_days,
        {
            {
                "NestedTable", 
                each Table.AddColumn(
                    _,
                    "prevDayBalance",
                    (r)=>
                    let 
                        key = r[Run Date], 
                        dateList = [Run Date],
                        balanceList = [#"Today's Balance"],
                        prevDatePosition = 
                            List.PositionOf(
                                dateList,
                                List.Max(
                                    List.Select(dateList, each _ < key)
                                ) 
                            ),
                        prevBalance = 
                            if prevDatePosition >= 0
                                then balanceList{prevDatePosition}
                                else null
                    in 
                        prevBalance,
                    type number
                ),
                type table [Day=text, #"Run Date"=nullable date, #"Portfolio Code" = number, #"Today's Balance"=number, prevDayBalance=nullable number]
            }
        }
    ),
    expand_nested_table = 
    Table.ExpandTableColumn(
        transform_nested, 
        "NestedTable", 
        {"Day", "Run Date", "Today's Balance", "prevDayBalance"}, 
        {"Day", "Run Date", "Today's Balance", "prevDayBalance"}
    )
in
    expand_nested_table

[GeraldGEmerick · Accepted solution]

@gmasta1129 I believe something like the following calculated column should work in DAX. In Power Query I feel like it would be more complex and you would have to create the previous day column and then join the table back to itself using that column and the original Run Date column and then expand the Today's Balance column. Somebody may have a more elegant solution.

Previous Day Balance = 
VAR _PortfolioCode = [Portfolio Code]
VAR _RunDate = [Run Date]
VAR _Weekday = WEEKDAY( [Run Date], 2 )
VAR _PreviousDay = IF( _Weekday = 1, ( _RunDate - 3 ) * 1, ( _RunDate - 1 ) * 1 )
VAR _PreviousBalance = FILTER( ALL( 'Table' ), [Portfolio Code] = _PortfolioCode && [Run Date] = _PreviousDay )
VAR _Return = MAXX( _PreviousBalance, [Today's Balance] )
RETURN _Return