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    ...
  • 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

     

     

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