Forum Discussion

parry2k's avatar
parry2k
Super User
8 years ago
Solved

Balance value calculation

Phil_SeamarkZubair_Muhammad Anonymous Vvelarde MFelix

 

Hello fellow datanuts, Attached is a sheet to calculate the value of balance and explained in "value calculation formula" how the value will be calculated. Balance value column is expected to be the result for each balance column, main columns are

 

Date

Item

In

Rate 

Balance

 

Basically calculating balance value from previous incoming entries.

 

How you guys will do it? Would like to pick your brain for the solution.

 

Thanks,

P

Need Help

6 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    parry2k

     

    I think this will work...But there could be an easier way

     

    Column =
VAR mybalance = Table1[Balance]
VAR temp =
    FILTER (
        Table1,
        Table1[Item] = EARLIER ( Table1[Item] )
            && Table1[Date] <= EARLIER ( Table1[Date] )
            && Table1[In] <> BLANK ()
    )
VAR temp1 =
    ADDCOLUMNS ( temp, "Rank", RANKX ( temp, [Date],, DESC, DENSE ) )
VAR temp2 =
    ADDCOLUMNS (
        temp1,
        "Cumulative", SUMX ( FILTER ( temp1, [Rank] <= EARLIER ( [Rank] ) ), [In] )
    )
VAR temp3 =
    FILTER ( temp2, [Cumulative] <= EARLIER ( Table1[Balance] ) )
VAR Fullrows =
    MAXX ( temp3, [Cumulative] )
VAR temp4 =
    TOPN (
        1,
        FILTER ( temp2, [Cumulative] > EARLIER ( Table1[Balance] ) ),
        [Date], DESC
    )
RETURN
    IF ( Table1[Balance] <> BLANK (), SUMX ( temp3, [In] * [Rate] ) )
        + SUMX ( temp4, ( mybalance - Fullrows ) * [Rate] )