Forum Discussion

wlknsnBI's avatar
wlknsnBI
Helper II
6 years ago
Solved

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 (lookin...
Need Help
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

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's avatar
    wlknsnBI
    Helper II
    6 years ago

    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.