Add current value column by subtracting current row from previous row

[AHMEDMARHOON]

Dear PBI Community, thank you in advance for the help

I am new to Power BI & DAX 

I have tow tables

1) flock table with an initial number of birds.

2) Birds mortality logs 

I want to add new colum in mortality log table wich gives the current birds count by subtracting previous mortality 

[tamerj1 · Accepted solution]

Hi @AHMEDMARHOON 

If you want to do it using DAX, you can first create a one to many relationship between the two tables based on flock id in both tables (that will make it much faster and easier)

the create New Column >

Balance No. of Birds =
VAR CurrentID = Mortality[id]
VAR FlockTable =
    CALCULATETABLE ( Motality, ALLEXCEPT ( Mortalit, Mortalit[flock_id] ) )
VAR PreviousIDsTable =
    FILTER ( FlockTable, Mortality[id] <= CurrentID )
VAR TotalMortality =
    SUMX ( PreviousIDsTable, Mortality[mortality] )
VAR FlockPopulation =
    RELATED ( Flocks[no_of_birds] )
RETURN
    FlockPopulation - TotalMortality