When calculating the total events per month, only count the event in the starting month

[WorkHard]

Trying to understand the following behavior.

Say I have 2 events and on average each event lasts at least 2 months.

Event ID	Start Date	End Date
1	1/1/2020	3/1/2020
2	4/1/2020	6/1/2020

When I create a very simple chart and display the total events per month (event id distinct count), the same event appears in 3 different months and gives the illusion that I have more events than there actually are.

I have a calendar linked to start date.

How can I only display the event in the month it started?

[mahoneypat]

The behavior you are looking for is the default behavior if there is a relationship between the Date table and the Start Date column.  Please make sure:

1. That relationship is active

2. The columns on both sides of the relationship are the same data type (Date not DateTime for example)

Regards,

Pat

[WorkHard]

Thanks, Pat.

1. Yes, the relationship between the Start Date and the date in the calendar table is active. 

2. Yes, both columns are the same data type (both are date/time).

What else could it be?

To clarify, the Grand Total is displayed correctly as 2 events but the breakdown by month still shows 6 (1 event for each month).

[V-lianl-msft · Accepted solution]

Hi @WorkHard ,

You can try to create a measure to distinctcount the event id:

Measure = var min_date = CALCULATE(MIN(Event[Start Date]),ALLEXCEPT(Event,Event[Event ID]))
var distinct_ =  CALCULATE(DISTINCTCOUNT(Event[Event ID]),FILTER(Event,Event[Start Date]=min_date))
return SUMX(VALUES(Event[Event ID]),distinct_)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.