Sum bank balance from one fact table and movement from another through date

[gustavocunha76]

Im new in Power BI and DAX language, but learning.. I have a mission to acumulate a cashflow value using 2 tables.

1 - fact_bank balance
2 - fact_movement 

I need to create a DAX formula do get a initial value from fact_bank and acumulate with the first value from the fact_movement table in order to crete a historical balance evolution. 

The thing is. I have a slice with defines de periodo in months ahead and i need to get de first date from the period which i want to project foward.

Is it possible? Thanks in advanced

[gustavocunha76]

Thank you for the help!!!

[v-shex-msft · Accepted solution]

Hi @gustavocunha76,

You want to add the movement calculate amount, right ?

If this is a case, you can refer to below steps:
1. Add index column(power query).
2. Write the measures to get previous value and calculate the change.

Previous = 
var lookup=LOOKUPVALUE(Fact_balance[value],Fact_balance[Bank_id],MAX(Fact_movenment[bank_id]),Fact_balance[date],MAX(Fact_movenment[due_date]))
var last_value=LOOKUPVALUE(Fact_movenment[acumulate],Fact_movenment[bank_id],MAX([bank_id]),[Index],MAXX(FILTER(ALL(Fact_movenment),[bank_id]=MAX([bank_id])&&[Index]<MAX(Fact_movenment[Index])),[Index]))
return
IF(MAX([Index])=MINX(ALL(Fact_movenment[Index]),[Index]),lookup,last_value)

change = MAX(Fact_movenment[acumulate]) -[Previous]

3. Use summarize function to get the result.

Result = ADDCOLUMNS(SUMMARIZE(Fact_movenment,[bank_id],[due_date],[Index],[acumulate],"Previous",[Previous]),"Value +",MAX(0,[acumulate]-[Previous]),"Value -",ABS(MIN(0,[acumulate]-[Previous])))

Regards,

Xiaoxin Sheng