Balance value calculation

[parry2k]

@Phil_Seamark@Zubair_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

[Zubair_Muhammad]

@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] )

[Zubair_Muhammad · Accepted solution]

@parry2k

File attached as well.

There are few differences

[parry2k]

@Zubair_MuhammadThanks will take a look and also wondering to see if anyone else reply on this and how they will solve it.

[parry2k]

@Zubair_Muhammadi would like to accept your solution but still waiting on other experts on how they will approach it? 

[Vvelarde]

@parry2k

I still trying to understand the logic involved in this.

Don't get it yet

Regards

Victor

[parry2k]

@Vvelardethanks for looking into it, basically it take the balance and go backward until balance quantity is zero, in other words, allocate balance quantity backward to the "in" Quantity and multiply allocated quantity with the rate.