Join tables

[Anonymous]

Hi All - 

I'm trying to create this visual in Powerbi. This is my old excel version, that is not very dynamic and requires a lot of manual entry.

The goal: pulling data from three sources....

1. Invoiced (this is in orange) it actuallly happened, each column represents a segment of our businesss. 

2. we do a current month projection ...you can see September has "RPC"after it. This comes from another source and it's our current month projection... 

3. Planned workload (gray) this is future months revenue that we have in the pipeline.

As each month passes, we fill up our invoicing bucket (orange) and the gray projection disappears.

Also - looking to set targets for each segment.... but struggling to even join my three sources together.

I thought CROSSJOIN might be the right DAX formula to use.

Second screengrab is my setup - "Targets" are the projection for future months (Gray), "Final Production" is my invoiced (actual) (orange) - third one is "current month RPC"- this is the projection for the current month as reported by our project managers. 

Any help is appreciated!!

[Anonymous]

Hi @Anonymous ,

I'm not clear about your requirement. What's your expected result? Orange part is the current data and grey part is the data for the future? Could you please provide some sample data (exclude sensitive data) in your data model and the related calculation logic of your expected result? Thank you.

Best Regards

Rena

[Anonymous]

The first photo is my expected result. I need to show 12 months of data benchmarked against a target.

The column would fill from bottom to top with Invoiced (actual) as months pass. The gray is projected.

We check in monthly with our project managers so the situation looks like this say, in October...

January through September would be gray (we have invoiced and it's real data), We are checking in and confirming October this week. So i'd have a second tier filling in October. From the "Current Month RPC" source... My third source would project/forecast November and December's $$. this is the source "PBI". 

We have 7 segments of our business so i'd like to be able to slice it by each segment (each column).

Then - a target could somehow be represented above each column. 

To make it more dynamic - I want to have my model look at the data in the "RPC Current month" (October's numbers) and IF a column in there contains yes or no, use data from that source, or look to PBI as a projection. SO becasue we have not completed the RPC Current Month, it would grab October numbers from PBI (an old projection).... after this week I would make a column YES in the RPC source, and it would grab that as a number rather than a projection for October from PBI. 

When we then INVOICE, those numbers flow to "Final Production" source and the process starts over. Looking to RPC, or PBI for most timely projection.