Forecast and Actual Sales - Plotting One Against The Other (Sales build each week)

[thomdeluca]

Scenario:
Have two tables: Sales and Forecast.
There are 4 Products that we "sell".
Forecast Table has forecasted sales by week, for each of the 4 products, for 52 weeks.
The Sales Table, updates each week, with that single-most current week's sales, by product.

I linked both tables.

When I Pivot, I only want the weeks that have sales data to return in my Pivot; recall, there are 52 weeks of forecasted data, and each week, another week's actual sales occur.
At this time, the Pivot is returning 52 weeks, with Forecasted values and blanks for the Sales Measure (as there are future weeks which sales haven't occurred yet).

My question: Is there a DAX formula that can filter to return only those weeks in which there are sales values? How might you write such a formula?
I can share the workbook.

 

[ruve1k]

Create a measure for Forecast like so:
ForecastMeasure:=IF( ISBLANK( [SalesMeasure] ), BLANK(), SUM( SAlesTable[SalesColumn] ) )
Use this measure instead of a raw sum of forecasted values. Since both [SalesMeasure] and [ForecastMeasure] will return blank when there are no sales, the weeks without sales should not show up in a pivot table with default settings.

 

[thomdeluca]

Create a measure for Forecast like so:
ForecastMeasure:=IF( ISBLANK( [SalesMeasure] ), BLANK(), SUM( SAlesTable[SalesColumn] ) )
Use this measure instead of a raw sum of forecasted values. Since both [SalesMeasure] and [ForecastMeasure] will return blank when there are no sales, the weeks without sales should not show up in a pivot table with default settings.

Thanks for your help.

I created my Forecast measure as follows: if(isblank(SALES[Total Units]),blank(),sum(SALES[MAP_Units]))

The measure Forecast does indeed only return a value in the date range which there are corresponding sales, however, the value returned is the Sales value, not the forecasted value.


Then, I made one change, if(isblank(SALES[Total Units]),blank(),sum(Forecast[ForecastUnits]))

This worked!

BIG THANKS TO YOU for your help!!

 

[ruve1k]

Sorry about that. I guess that's what happens when answering questions on the forum after midnight.
The example should've been:

ForecastMeasure:=IF( ISBLANK( [SalesMeasure] ), BLANK(), SUM( ForecastTable[ForecastColumn] ) )

 

[thomdeluca]

Sorry about that. I guess that's what happens when answering questions on the forum after midnight.
The example should've been:

ForecastMeasure:=IF( ISBLANK( [SalesMeasure] ), BLANK(), SUM( ForecastTable[ForecastColumn] ) )

No apologies necessary. You're reply was spot on, and challenged me to learn too. All good.

BTW - I did post another question this morning (regarding creating a cumulative sales/sum measure). Would you mind taking a look?

Thanks for all!