Forum Discussion
Months between two dates (+decimals)
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
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.
4 Replies
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.
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.
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
Bit different but also on similar line
https://www.dropbox.com/s/5wo5jhzl488zy69/Minute_between%20Hours.pbix?dl=0
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.
