cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## [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.

Community Support

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors