Subtract months from two date columns, and drill down on date

[msuser48]

I have this table:

ID	StartDate	EndDate	Year	Month (must be calculated column)
1	2000-01-01	2001-01-01	1	0
2	2000-01-01	2002-01-02	2	1
3	2000-01-01	2000-29-12	0	11
4	2000-01-01	2003-01-06	3	5
5	1990-01-05	1995-01-07	5	2
6	2000-01-01	2005-01-06	5	5

I want to solve 2 problems:

PROBLEM 1)

How do I create the desired output (month column) in a calculated column? I need to the month value of EndDate minus the month value of StartDate to do this, I suppose.

- Example: For ID 3: 12 minus 01 gives the correct value = 11.

PROBLEM 2)
When I have this above table with year and month, I need a column chart with this drill down logic:

- This below shows how many rows have the 0, 1, 2, 3, 4, and 5 value in the year column.

If I then click "0", it should drill down, and show me how many rows, for year 0 only, there are for each month value, like this:

How to accomplish this?

[Mahesh0016]

In drill down you should TURNOFF Keep All Filters .

[Mahesh0016]

Month = MONTH('Subtract months'[EndDate])-MONTH('Subtract months'[StartDate])

for month solutions.

[amitchandak]

@msuser48 , Based on what I got two columns 

Year = Quotient(datediff([Start Date], [End Date], Month)+1, 12)

Month= Month(datediff([Start Date], [End Date], Month)+1, 12)

[msuser48]

@amitchandakthanks, but this doesn't work. It says "too many arguments passed to month function. Max argument count is 1.

For the second problem also, how do I make the drill down described in problem 2, if you know?

[v-shex-msft · Accepted solution]

Hi @msuser48,

According to your description, it seems like you want to get the diff on month level of date values.

If that is the case, you can try to use month function to extract the month number from 'start/end' date, then you can simply to get the difference.

diff =
MONTH ( [End Date] ) - MONTH ( [Start Date] )

Regards,

Xiaoxin Sheng