Forum Discussion

AHMEDMARHOON's avatar
AHMEDMARHOON
Frequent Visitor
4 years ago
Solved

Add current value column by subtracting current row from previous row

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's avatar
    tamerj1
    4 years ago

    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

     

     

1 Reply

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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