How to find variance between columns from separate tables

[Analytics4Life1]

Here is my data: https://ibb.co/1bH6QSF

I am trying to find the variance (difference) between the first and middle table in terms of expected sales and sale total (these are the actual sales), at the product category level (Table 3), for each year, quarter, and month.

How would I go about doing this? I suppose there are two preliminary steps:

1) Create a date-variable that links to the 3 tables.

2) Create a model that links the three tables together

The given date variables are weird, however, e.g. in table 1, I have "Year" and "month" in two separate columns. How do I consolidate this?

Ultimately, I want to create something similar to this: https://ibb.co/r4rgDfS

That can show me the "actual sales"  (total sale in my data) vs "target sales" (forecasted sales in my data), at a Year, quarter, or month level.

Any help is appreciated, thank you!

[Analytics4Life1]

Hi Nathaniel, this is perfect, and I think I can take it from here 🙂

[Nathaniel_C]

Hi @Analytics4Life1 ,

Actually I wrote that for a measure, not a column. So if you use it as a column, you don't need the MAX() functions. Sorry didn't see that until I looked again at the picture.
Nathaniel

[Nathaniel_C · Accepted solution]

Hi @Analytics4Life1 ,

new date = CONCATENATE("1/",MAX('Table (2)'[Month]))&"/"&max('Table (2)'[Year ])//You can use either CONCATENATE() (which only takes two values, or & to combine Text. Nee to use quotes around any text.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

If you would share your pbix, or dummy up some values in Excel both for current and expected data. Please copy and paste them into your post, rather than doing a picture, we may be able to help you. 

Please read this post to get your question answered more quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490