Dax formula for 2 tables (related)

[cristianml]

Hi,

I have 2 tables related and I need a Dax formula to follow this logic: 

IF "Actual or forecast" column in Ad hoc table IS EQUAL TO "Actual or forecast" column in Forecast table THEN

SUM  Amount (Ad hoc table) + SUM  Amount (Forecast table) 

IF IS NOT EQUAL THEN 

SUM SUM Amount (Forecast table)

Ad hoc table
PostingPeriod	Amount	Actual or Forecast
19-Jan	12,000.00	Actual
19-Feb	12,000.00	Actual
19-Mar	12,000.00	Actual
19-Apr	12,000.00	Actual
19-May	12,000.00	Actual
19-Jun	12,000.00	Actual

Forecast table
PostingPeriod	Amount	Actual or Forecast
19-Jan	-	Actual
19-Feb	-	Actual
19-Mar	-	Actual
19-Apr	-	Actual
19-May	12,000.00	Forecast
19-Jun	12,000.00	Forecast
19-Jul	12,000.00	Forecast
19-Aug	12,000.00	Forecast

This should be the result for the DAX formula:

19-Jan	19-Feb	19-Mar	19-Apr	19-May	19-Jun	19-Jul	19-Aug
12,000.00	12,000.00	12,000.00	12,000.00	12,000.00	12,000.00	12,000.00	12,000.00

This is the model :

Thanks.

[v-xicai · Accepted solution]

Hi @cristianml ,

You can try to create measure like DAX below.

Measure1= 
Var s1=CALCULATE(SUM('Ad hoc table'[Amount]),FILTER(ALLSELECTED('Ad hoc table'), 'Ad hoc table'[PostingPeriod] =MAX('Ad hoc table'[PostingPeriod])))
Var s2=CALCULATE(SUM('Forecast table'[Amount]),FILTER(ALLSELECTED('Forecast table'), 'Forecast table'[PostingPeriod] =MAX('Forecast table'[PostingPeriod])))
Return 
IF(MAX('Ad hoc table'[Actual or Forecast])=MAX('Forecast table'[Actual or Forecast]),s1+s2,s2)

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.