challenges with cluster/stacked and multiple dates

[Anonymous]

Hello -  I have a specific request to replicate a stacked/clustered chart that my boss does in Excel.   This type of chart is not native to Power Bi, but one was available in the marketplace that is really close.    An example of this is below, and how the table needs to be laid out to acheive the chart.  

My challenge is that I have Projections and Actuals, and they are "attached" to different date periods.   Projections (orders and forecasts).   Orders are connected to my date table (Dates - Due Dates).   Forecasts are connected (Dates - Est Close Date).      Actuals are shipments...and they are connected to my date table  (Dates - Date Shipped).   

Both Actuals and Projections consist of three "types" of revenue.   Forecasted revenue at 80%.   ERP orders.   Unforecasted orders.   

I somehow need to be able to total these up with their correct totals, but also be able to use a global date filter.   What happens now is my date table is only filtering one category (actual or projection) at a time.   But I have values (like forecasts and orders) whose total sums are both based on different date fields.

[MFelix]

Hi @Anonymous ,

I have some questions regarding your setup:

• When you refer about having diferent values linked to that date tables (Orders, Forecast, Actuals) we are talking about 3 different tables correct?
• On the table last table you present you want to split the values in projects/actuals is this the main objective?

Just as general overview, using a model with several linked tables and a stacked bard chart with categories can lead you results you don't want.

In this case I believe you need to use several measures and maybe a disconeccted date table.

Can you share a more infomration about your data and expected results.