calculating a collumn per row level

[Anonymous]

Hi everyone,

i have the following data set

With each row represents the moviment in a account,i tried to create a Measure where i have to do  Credit - Debit while i filter everything that if before my "DateTime" collumn, but it doesnt work the way i imagined, i did a model of what i expect in excel

Basically i need to use my Measure Column deduct my values per row level,  (5555-2246 = 3309) ,(3309-4465 = -6711)and goes on to give the results of my  "Expected Value"

Thanks everyone

[Ashish_Mathur]

Hi,

How did you arrive at the number -6.711?  A mystery indeed.

[Anonymous]

My mistake, the excel formula was wrong when i created that example, this one is the new and right number

[Ashish_Mathur]

Hi,

This approach will work:

1. Remove time stamp from the DateTime column
2. Create a Calendar Table
3. Build a relationship from the Date column (from which you seperated the time stamp) to the Date column of your Calendar Table
4. To your visual, drag the Date from the Calendar Table
5. Write these measures

Total debit = SUM(Data[Debit])

Total credit = SUM(Data[Credit])

Net = [Total credit]-[Total debit]

Running net = calculate([net],datesytd(calendar[date],"31/12"))

Hope this helps.

[mahoneypat]

Please try this expression, replacing Table1 with your actual table name.

Total =
VAR thisdate =
    MIN ( Table1[Date] )
RETURN
    CALCULATE (
        SUMX (
            Table1,
            Table1[Credit] - Table1[Debit]
        ),
        ALL ( Table1 ),
        Table1[Date] <= thisdate
    )

Regards,

Pat

[Anonymous]

Thats it!! this is worked in my sample, but when i tried in my original file (with a slicer) the measure table started to loading and dont display the measure @mahoneypat 

(My original file, if you can see the loading icon on my table top, i just used the filter os the same table)

Here i used a filter too, but the measure worked perfectly

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Would you please try to use the following measure:

final balance = CALCULATE(SUM('Table'[Credit])-SUM('Table'[Debit]),FILTER(ALLSELECTED('Table'),'Table'[DateTime]<= MAX('Table'[DateTime])))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai