cancel
Showing results for
Did you mean:
cristianml Member

## Dax formula for 2 tables (related)

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.

1 ACCEPTED SOLUTION

Accepted Solutions Community Support Team

## Re: Dax formula for 2 tables (related)

Hi @cristianml ,

You can try to create measure like DAX below.

```Measure1=
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. Community Support Team

## Re: Dax formula for 2 tables (related)

Hi @cristianml ,

You can try to create measure like DAX below.

```Measure1=
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.

Announcements #### Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future. Learn the answers to some of the questions asked during the Amanda Triple A event. #### October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.  Top Kudoed Authors
Users Online
Currently online: 343 members 2,628 guests
Recent signins:
• MICOLSCOTT • Vamshi_R • vtruong • StidifordN • kwilky • chadrenstrom • adamnimmo • prescient • priscilajardim • pkasa • bcutler1996 • gbelks • TTZ • AugustsHarvest 