Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |