Reporting on fields connected to 2 calendar tables

[Davebryant1]

Hi all,

I have a table that contains start and end dates. I want to be able to count both collumns and display by month. The table looks like this

Table1

Start date	End Date
15-Jun-22	24-Jun-22
14-Jul-22	23-Jul-22
18-Jul-22	27-Jul-22
16-Sep-22	25-Sep-22
20-Oct-22	29-Oct-22
10-Jun-22	19-Jun-22
27-Jun-22	06-Jul-22
18-Jul-22	27-Jul-22
18-Jul-22	27-Jul-22
26-Jul-22	04-Aug-22
12-Aug-22	21-Aug-22
09-Sep-22	18-Sep-22
28-Sep-22	07-Oct-22
03-Oct-22	12-Oct-22
11-Oct-22	20-Oct-22
11-Oct-22	20-Oct-22
08-Jun-22	17-Jun-22

Output requrired

	Start date Count	End Date Count
Jun-22	4	3
Jul-22	5	5
Aug-22	1	2
Sep-22	3	2
Oct-22	4	5

I have a calender table connected with table 1 but its join is based on the starte date. This means reporting start dates are fine but when trying to add the end date its not giving me accurate figures beacsue of the join. 

I can obviously create a new calendar table and join with the end date but im unsure of how i then get both sets of data onto the same table?

Thanks

[v-jianboli-msft]

Hi @Davebryant1 ,

Based on your description, I have created a date table:

Then create the measures:

Start date Count = 
var _Year = YEAR(MAX('Date'[Date]))
var _Month = MONTH(MAX('Date'[Date]))
return CALCULATE(COUNT(Table1[Start date]),FILTER(ALL('Table1'),YEAR([Start date])=_Year&&MONTH([Start date])=_Month))

End date Count = 
var _Year = YEAR(MAX('Date'[Date]))
var _Month = MONTH(MAX('Date'[Date]))
return CALCULATE(COUNT(Table1[End Date]),FILTER(ALL('Table1'),YEAR([End Date])=_Year&&MONTH([End Date])=_Month))

Final output:

Best Regards,

Jianbo Li

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

[Davebryant1]

Hi Jianbo,

Thanks for your response, it worked perfectly.

How would i go about adding an additional filter onto the end date measure?

I want the same measure you'd already written but i want it to filter on an additional field in Table 1 too. I.e. 

Table1[property type] = "Type 1"

Many Thanks

[v-jianboli-msft · Accepted solution]

Hi @Davebryant1 ,

Please try:

End date Count = 
var _Year = YEAR(MAX('Date'[Date]))
var _Month = MONTH(MAX('Date'[Date]))
return CALCULATE(COUNT(Table1[End Date]),FILTER(ALL('Table1'),'Table1'[property type] = "Type 1"&&YEAR([End Date])=_Year&&MONTH([End Date])=_Month))

Best Regards,

Jianbo Li

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