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 ...
Need Help
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] )
    • parry2k's avatar
      parry2k
      Super User
      8 years ago

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

      • parry2k's avatar
        parry2k
        Super User
        8 years ago

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