Summarize sales by month including blanks and zeros

[gdecome]

Hi all ... not sure if the best way to manage this request is using DAX or Power Query. Despite any option, how to do it.

I have a table with Sales_Rep names (first table below) and start dates. Another table with sales by Sales_Rep (second table) and I need to summarize by month (third table) including the volume for each sales rep. The summary should show blanks if the rep was not hired yet and 0 if there was no sales at that month (after hiring).

Any suggestion? Thanks in advance

Sales_Rep  Start_Date

Rep01	12/15/2019
Rep02	02/05/2020
Rep03	03/20/2020

Sales_Rep Sales_Date Units

Rep01	01/05/2020	5
Rep01	02/08/2020	3
Rep01	03/04/2020	4
Rep01	04/02/2020	7
Rep01	05/01/2020	8
Rep02	02/02/2020	2
Rep02	03/03/2020	1
Rep02	05/05/2020	2
Rep03	04/04/2020	1
Rep03	05/05/2020	2

	Jan-20	Feb-20	Mar-20	Apr-20	May-20
Rep01	5	3	4	7	8
Rep02		2	1	0	2
Rep03			0	1	2

[v-diye-msft]

Hi @gdecome 

kindly check my below results:

Measure = IF (
 MAX ( 'Table 2'[Column] ) < MAX ( 'Table (2)'[Column 2]),
 BLANK(),CALCULATE(
 IF (
 MAX ( 'Table 2'[Column] ) >= MAX ( 'Table (2)'[Sales_Date] ),
 MAX('Table (2)'[Units] ),
 IF (
 MAX ( 'Table 2'[Column] ) >= MAX ( 'Table (2)'[Column 2] )
 && ISBLANK ( MAX ( 'Table (2)'[Units] ) ),
 0
 )+0
 )
,USERELATIONSHIP('Table (2)'[Column],'Table 2'[Column]))+0)

Pbix attached.

[gdecome · Accepted solution]

This is great!

Thank you very much @v-diye-msft 

[amitchandak]

@gdecome , Join both tables on sales_rep. Join sales table with a date table having month year in that

and use this formula

sales = calculate(sum(sale[Units]), filter(Sales, sales[Sales_Date]>=related(Sales_Rep[Start_Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[gdecome]

Thank you @amitchandak , I will test and let you know

[AllisonKennedy]

@gdecome  Do you have a DimDate table?

https://allisonkennedycv.blogspot.com/2020/04/dimdate-what-why-and-how.html

If you have the date table , then this will be relatively simple DAX query. 

One question - do we need to check the start date or can we assume they won't have any sales if they haven't started?

Please provide your table names and answers to my above questions and we can help you further. 

[gdecome]

Hi @AllisonKennedy , thank you for the quick reply.

About your questions

• If you have the date table , then this will be relatively simple DAX query.
  • Yes, I have a Calendar table that could be used on this 

• do we need to check the start date or can we assume they won't have any sales if they haven't started?
  • No, it should be blank anyway if they were not hire yet (at that month) considering they don´t have access to register.
  • If not started, shows blank in the month. If started but no sales in the month, shows zero.

• Please provide your table names and answers to my above questions and we can help you further.
  • Calendar
  • Sales_Rep    (name and hiring date)
  • Sales            (name, sales date and units sold)

[AllisonKennedy]

@gdecome  Ok, I see what you mean now, you will need to check start date to get blank vs 0.

Using the Calendar table in the Matrix, you should be able to apply this MEASURE:

Total Units = IF(MIN(Sales_Rep[StartDate])>=MIN(Calendar[Date]),0+ SUM(Sales[Units]))

[gdecome]

Hi @AllisonKennedy it didn´t work yet ... it is showing just months related to Start_Date despite the Total Column with the right values (measure is similar to the one provided by @amitchandak too)

Total Units 2:=IF(MIN(Sales_Rep[Start_Date])>=MIN(Calendar[Date]),0+SUM(Sales_Units[Units]))

Obs: Relationships

Calendar / Sales_Rep is 1:1 Direction=Both

Calendar / Sales_Units is 1:n Direction=Single

Name    Dec-19  Feb-20   Mar-20  Total 

Rep01	0	3	4	27
Rep02	0	2	1	5
Rep03	0	0	0	3

[amitchandak]

@gdecome , please find the attached file after signature, I am getting same result as first table

[gdecome]

Thanks @amitchandak , I was better testing you formula and get the same result as yours.

Numbers are fine but one thing still missing is the "zero" after hiring date. For example, Rep02 should show "0" in Apr-20 as well as Rep03 should show 0 inMar-20 that is hiring date but no sales in that month.

In other words, show blank if not hired yet but show zero if hired but no sales for that period.

[gdecome]

Great, thank you @AllisonKennedy  ... I will test and let you know.