daily sales v monthly target by sales team

[kevin_wright]

Hi All

Im a newbie at powerbi and trying to hash together a sales report for my employer. Im specifically stuck on getting the sales (which is a list of daily activity) to compare against a monthly target for different teams.

It keeps bringing in the monthly target as a straight line across the months when put into a column and line graph (when it actually varies month to month)

Source of data is excel.

Tables:

Date table – used DAX to create every single date between 2018/04/01 and 2022/03/31 (Date = CALENDAR(date(2018,04,01),date(2022,03,31))

Also used DAX to create a month column in the same table (Month = format('Date'[Date],"mmm-yy")) ,  but that repeats for every single day (so its not a unique entry when trying to create a 1 to many table relation on the month)

Sales table – contains date (linked to date table), territory, product type, qty of product, price per product, order number, freight, discount, total sales value for order etc This has a row for every transaction every day so nothing is unique.

Product Types - list of unique product types which I link to sales table (product type field) so I can report on product groupings

Territory – list of unique territory names which I link to sales table (territory field)

Territory Target –contains a column for territory (there is about 6 territories), month, target, and a cumulative target

Eg

Team1,  apr-18, 10,000, 10,000

Team1, may-18, 12,000, 22,000

Team2, apr-18, 8,000, 8,000

Team2 may-18, 9,000, 17,000

Etc..

So territory and month are not unique as they repeat for each month of the year.

So how do I link the daily sales ,to monthly target? Neither has a 1 to many relation (always get the many to many and the issues that causes)

Should I separate this out further in excel before bringing into powerbi?

In excel I can formulate the monthly sales totals in the same table as territory target, to then allow this to pull together in powerbi, but it would be nice to understand how this can be done in powerbi without doing it all in excel.

Many thanks in advance– and please explain to the greatest detail possible, as I said Im a newbie and using google to find my way around all this.

Thanks

Kev

[lbendlin · Accepted solution]

Your link from Date table to Sales table should be on the date, and it should be 1:M.

In your Target table create another column with an artificial date for each month (for example first day of the month). Link that to the Date table, should be 1:1 but you can change the filter direction to be single way from Date to Target.