Use Actuals values in months available otherwise use Forecast

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
167
Office Version
  1. 365
Platform
  1. Windows
Hi,

I'm having difficulty writing a measure that will for each month only pull through actuals values if there are any actuals values otherwise it should pull through the forecast values.
So say a company is 4 months through its Fiscal Year it would show 4 months of actuals then the 8 months of forecast values.

The problem if Power BI using my measure below is pulling through values on a row-by-row basis. The £10,000 values below are all dummy figures from the forecast. As the other values are actuals I'd not like any of those forecast values to show in that month.

If you could please help me tweak my measure that would be much appreciated.

1688532256500.png


1688532251483.png


Thanks,

Paul
 

Attachments

  • 1688532101717.png
    1688532101717.png
    17.8 KB · Views: 7

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,223,956
Messages
6,175,619
Members
452,661
Latest member
Nonhle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top