topic Add current value column by subtracting current row from previous row in DAX Commands and Tips

Add current value column by subtracting current row from previous row

AHMEDMARHOON — Sun, 17 Apr 2022 06:35:49 GMT

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

Re: Add current value column by subtracting current row from previous row

tamerj1 — Sun, 17 Apr 2022 12:28:30 GMT

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