Skip to main content
cancel
Showing results for 
Search instead 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

Reply
djb_bc
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.


ClosingBalance.jpg
 Can anybody help with the solution please.

Cheers.

1 REPLY 1
v-xinruzhu-msft
Community Support
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

vxinruzhumsft_0-1682908998733.png

 

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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