Sales vs Target Matrix Comparison

[nnouchi]

Greetings all,

I’m looking to get advice regarding a historical sales comparison. I have two tables, actual invoice sales and target invoice sales. The issue is that my target sales are broken down by monthly targets instead of having a daily value to compare to my invoice sales table.

My sales manager wants to see a side by side (same visual) view and at the moment I can’t seem to figure out how to compare the two figures within a single chart.

My target sales table can be linked back to actual sales by an identifier Territory_ID.

I’ve created a Qtr/Month/Year key in my Sales Targets table tied back to the Dates table with the same key, which allows me to use this on a high-level scale.

Here’s the structure of the table:

Now when I compare it on a matrix to look at actual vs targets, the result doesn’t provide targets for years other than 2018.

My RM_Target table is linked to my date table in my relationships. The way I currently tie these two tables is based off of the QTR/MONTH/YR column --> 

Target Sales = CALCULATE(Sum(RM_Target[Target_amount]),USERELATIONSHIP(RM_Target[QTR/MTH/YR],Dates_V2[QTR/MNTH/YR]))

Any help would be greatly appreciated.

[v-chuncz-msft]

@nnouchi,

You may try to apply virtual relationship in DAX measure.

https://www.sqlbi.com/articles/physical-and-virtual-relationships-in-dax/