Months between two dates (+decimals)

[wlknsnBI]

Hi,

I'm looking to get the months between two dates, plotted onto table like below (using calendar table probably). What would be the measure to make that happen to include partial months (looking at total days of month and dividing how much left or have allready passed)?

Start	End
10/09/2019	18/01/2020
		Months
		201909	201910	201911	201912	202001			2019	2020
		0,666667	1	1	1	0,580645			3,666667	0,580645

Big thanks

[v-diye-msft · Accepted solution]

Hi @wlknsnBI 

Please kindly check below results:

Table = ADDCOLUMNS(CALENDAR(DATE(2019,01,01),DATE(2020,12,31)),"YearM",YEAR([Date])*100+MONTH([Date]))

Measure = var y = MAX('Table'[YearM])
return
DIVIDE(COUNTROWS('Table'),CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[YearM] = y)))

 

Pbix attached.

[amitchandak]

@wlknsnBI ,

Refer to file for the solution you are looking for. You just need to take care how you want to datediff. I included first and last date

https://www.dropbox.com/s/yuv64v0cneseghx/value%20Split%20between%20months%20start%20end%20date.pbix...

Bit different but also on similar line

https://www.dropbox.com/s/5wo5jhzl488zy69/Minute_between%20Hours.pbix?dl=0

[Greg_Deckler]

Right, so you are going to want something along the lines of Hour Breakdown:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Hour-Breakdown/m-p/625085#M306

Remember that Dates/Times are just decimal numbers the whole part of the number is the number of days and the decimal is time. So, you could construct a measure like the Hour Breakdown that returns 1 if the entire month is spanned and in the situations where there is a partial month, do the simple calculation to compute the part of the month. Remember that all months have a 1st and you can use EOMONTH([Month],0) to get the end of the month.

[wlknsnBI]

I was actually thinking of something more simple. First of all, calendar table to match which month is being selected (for the table) between two dates.  After a which I calculation for which the selected applies.