[Cash Closing Balance] respective to date context

[djb_bc]

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.

[v-xinruzhu-msft]

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.