Calculate user balance for a specific date

[Milan14]

Hi everyone,

I would like to calculate the user balance for a specific month. 

Sample data:

When I applied filter [Year] = 2022 and [Month] = December, it should take the balance with GREEN highlight.

Expected outcome:

Does anyone have any ideas what kind of formula I should apply?

Any help would be much appreciated.

Thanks in advance!

Kindly Regards,

Milan

[Milan14]

Hi @tamerj1,

Sorry for the let reply..

Thanks for your help. It's exactly what I need !   

Milan

[tamerj1 · Accepted solution]

Hi @Milan14 
Please refer to attaches sample file with the solution

Balance Measure = 
VAR SelectedDate = MAX ( 'Date'[Date] )
VAR Result =
    SUMX ( 
        VALUES ( 'Table'[UserID] ),
        VAR CurrentIDTable = CALCULATETABLE ( 'Table' )
        VAR MaxDateRecord = TOPN ( 1, FILTER ( CurrentIDTable, 'Table'[Date] <= SelectedDate ), 'Table'[Date] )
        RETURN
            MAXX ( MaxDateRecord, 'Table'[Balance] )
    )
RETURN
    Result