Include 'actual' values only if a 'forecast' value exists for the same date/category

[Anonymous]

My table is similar to the one below. It represents brick sales as of January 1950. Forecasted values are projected for Feb and March.

MonthYear              Category           Forecast/Actual         Value

Jan 1950                 Brick                   Forecast                   200

Feb 1950                 Brick                   Forecast                   300

Mar 1950                Brick                   Forecast                   420

Jan 1950                 Brick                   Actual                        250

Feb 1950                 Brick                   Actual                       0

Mar 1950                 Brick                  Actual                        0

I want to get the sum of forecasted values - but ONLY for the MonthYear values which have an 'Actual' value which is > 0. 

My knowledge of DAX is currently limited, and I've tried countless ways to do this without success (including a few table transformations, which didn't help and duplicated the 'actual' values for some reason! Is there any way to do this using the table above? 

Thanks. 

[samdthompson · Accepted solution]

Hello, If I were you, I'd manipulate the table in power query for loading. Pivot the Forecast/Actual column over the Values. That way you can to a simple suming calculation rather than trying to DAX your way around a table tructure thats not quite right.

Cheers,

[Anonymous]

@samdthompson you're right, and that was my preferred approach. Unfortunately, the specific circumstances concerning my data didn't allow me to place the actual/forecast in the same rows, which led to peculiar results. I was therefore forced to use this approach. Thanks anyhow 🙂 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[samdthompson · Accepted solution]

Hello, If I were you, I'd manipulate the table in power query for loading. Pivot the Forecast/Actual column over the Values. That way you can to a simple suming calculation rather than trying to DAX your way around a table tructure thats not quite right.

Cheers,