Forum Discussion

gmasta1129's avatar
gmasta1129
Resolver I
9 months ago
Solved

Previous Day Balance - Excluding Weekends

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 c...
Need Help
  • GeraldGEmerick's avatar
    GeraldGEmerick
    9 months ago

    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

     

jgeddes's avatar
jgeddes
Super User
9 months ago

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