Aggregate daily sales and compare to monthly targets

[erezbenmoshe]

I have daily sales data in a SALES TABLE with two columns: ORDER PURCHASE_DATE and ORDER TOTAL.  I also have targets table that has two columns: MONTH and TARGET SALES.

I have to build a measure that aggregates sales by month and compare them to the monthly target.

The output should be:  % of TARGET = DIVIDE(MONTHLY SALES, MONTHLY TARGERT).

Thanks,

Erez

[tex628]

Start by creating a connection between the tables. 

I would proberbly use:

Month = MONTH('sales table" [ORDER PURCHASE_DATE])

Then create a relationship to the target sales table.

Finally create a measure,

% of target = ORDER TOTAL / TARGET SALES

[erezbenmoshe]

Thanks for your answer.  I probably was not clear enough in my description.

Target SalesSales Table

The TARGET SALES table holds target sales on a month level (so 12 rows).

The SALES TABLE holds sales by day so I have to aggregate sales to month level  first ('000s of rows).

[tex628 · Accepted solution]

But drop a calculated column in the sales table:

Month = MONTH('Sales table'[Order purchase date])

This should allow you to create a one to many relationship from the target table to the sales table (Month to month*)

Then create a measure like this: 

TotalOrderSum = Sum('Sales table'[Total Order])

Finally create a table visual and put in:
*Month from taget table
*TotalOrderSum measure
*Target volume from target table