Forum Discussion

djb_bc's avatar
djb_bc
New Member
3 years ago

[Cash Closing Balance] respective to date context

Hi.
I have a DAX calc that needs solving, hopefully someone has more brain-power than me to solve it as I'm at a loss.

 

Essentially I want to get the latest non empty Ledger[CashClosingBalance] within the context of whatever date I'm pivotting on, usually by month.

The Ledger table is such that it has [PlayerId], [ActivityDate] and [CashClosingBalance].

The slight complexity is that not every player has a record for every month. So if I want to look at balances as at the end of April, a player's last entry in the table may be as far back as Feb, but I still want to include this as their balance hasn't changed and needs to be brought-forward. A simplistic view is seen here.



 Can anybody help with the solution please.

Cheers.

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi djb_bc 

    You can create a column in the table

    Column =
VAR a =
    MAXX (
        FILTER ( Ledger, [PlayerId] < EARLIER ( Ledger[PlayerId] ) ),
        [PlayerId]
    )
VAR b =
    FILTER ( Ledger, [PlayerId] = a )
RETURN
    IF (
        EOMONTH ( [Activity Date], 0 ) <> EOMONTH ( MAXX ( b, [Activity Date] ), 0 ),
        MAXX (
            FILTER ( b, [Activity Date] = MAXX ( b, [Activity Date] ) ),
            [CashClosingBalance]
        )
    )

    Then create a measure in the table

    Measure=SUM(Ledger[CashClosingBalance])+CALCULATE(SUM([Column]),ALLSELECTED(Ledger),Ledger[Activity Date]<=SELECTEDVALUE(Ledger[Activity Date]))

    Output

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.