Forum Discussion
Responsive ResidentIf Field is blank, use another formula
Hi all
I need help combing 2 measures in one.
Every month I received a manual Excel file with Finance Actuals from previous months
| Date | Country A Prod 1 | Country A Prod 2 | Country A Total | Country B Prod 1 | Country B Prod 2 | Country B Total |
| 7/1/2022 | $36,541 | $0 | $36,541 | $135,488 | $63,099 | $198,586 |
| 8/1/2022 | $7,628 | $0 | $7,628 | $12,790 | $275,085 | $287,875 |
| 9/1/2022 | $19,894 | $7,206 | $27,100 | $16,176 | $129,490 | $145,666 |
| 10/1/2022 | ||||||
| 11/1/2022 | ||||||
| 12/1/2022 | ||||||
| 1/1/2023 | ||||||
| 2/1/2023 | ||||||
| 3/1/2023 | ||||||
| 4/1/2023 | ||||||
| 5/1/2023 | ||||||
| 6/1/2023 |
On the other hand, I have the USD field in another table called "weekly Slippage" that I can filter x month.
What I need is a formula that uses the Finance numbers (Monthly Actuals Table) and , if blank, uses the USD field from the Weekly Slipapge table.
HI Mangaus1111
Thanks for your comment. Tried the Coalesce function but not really working.
Found another possible solution:
Actuals =VAR Actuals = 'Monthly Actuals'[All Countries Actuals]RETURNIF (ISBLANK (Actuals), 'Weekly Slippage'[FY23_Starts],Actuals)whereFY23_Starts = CALCULATE(SUM('Weekly Slippage'[USD]), DATESBETWEEN('Weekly Slippage'[Ops Forecast & Actuals], DATE(2022,7,1), DATE (2023,06,01)))But now i have another issue:IT seems it works for the "All countries Actuals"Actuals numbers for July, August and September = All Countries ActualsTHe thing is that for Oct to Jun Actuals numbers should match FY23_Starts but it's not trully happening. It seems is adding estra USD amount and I don't know why.jgeddes (in case you have time. thanks)
3 Replies
- mangaus1111
Solution Sage
Hi romovaro .
I think you can use the COALESCE function
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
romovaroHI Mangaus1111
Thanks for your comment. Tried the Coalesce function but not really working.
Found another possible solution:
Actuals =VAR Actuals = 'Monthly Actuals'[All Countries Actuals]RETURNIF (ISBLANK (Actuals), 'Weekly Slippage'[FY23_Starts],Actuals)whereFY23_Starts = CALCULATE(SUM('Weekly Slippage'[USD]), DATESBETWEEN('Weekly Slippage'[Ops Forecast & Actuals], DATE(2022,7,1), DATE (2023,06,01)))But now i have another issue:IT seems it works for the "All countries Actuals"Actuals numbers for July, August and September = All Countries ActualsTHe thing is that for Oct to Jun Actuals numbers should match FY23_Starts but it's not trully happening. It seems is adding estra USD amount and I don't know why.jgeddes (in case you have time. thanks)
- romovaro
It seems the new measure was not taken into account the "Filter on this page".
had to create a new measure including the filter.
thanks.
