Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |