Months between today and a date in a table

[sburtonprovenit]

I am working on an aged inventory report and I have my aging buckets determined by the  difference in months from purchase date to today.  Where my issue is occurring is when an item was purchased say June 14th of 2023.  Based on my datedif of monthly this is showing up in my 12 month bucket.  How can I get my report to recognize that this item should fall into the 12 month bucket on June 14th of this year.  My thought is I will have to use Days in my datedif calculations and then determine the number of days in my aging buckets (12 Mth, 18 Mth, 24 Mth).  If I have to go this route how do I determine how many days are in 18 Months could be different depending on the dates.

[sburtonprovenit]

Thanks that worked

[Greg_Deckler · Accepted solution]

@sburtonprovenit Try this measure or column. PBIX is attached below signature.

ToHellWithDATEDIFF = 
    VAR __Today = TODAY()
    VAR __Date = MAX([Purchase Date])
    VAR __Years = SELECTCOLUMNS( GENERATESERIES( YEAR(__Date), YEAR(__Today), 1 ), "__Year", [Value] )
    VAR __Months = SELECTCOLUMNS( { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 }, "__Month", [Value] )
    VAR __Table = ADDCOLUMNS( CROSSJOIN( __Years, __Months ), "__YearMonth", [__Year] * 100 + [__Month] )
    VAR __PreResult = COUNTROWS( FILTER( __Table, [__YearMonth] <= YEAR( __Today) * 100 + MONTH( __Today ) && [__YearMonth] >= YEAR( __Date ) * 100 + MONTH( __Date ) ) )
    VAR __Result = IF( DAY( __Today ) < DAY( __Date ), __PreResult - 1, __PreResult )
RETURN
    __Result

Column = 
    VAR __Today = TODAY()
    VAR __Date = [Purchase Date]
    VAR __Years = SELECTCOLUMNS( GENERATESERIES( YEAR(__Date), YEAR(__Today), 1 ), "__Year", [Value] )
    VAR __Months = SELECTCOLUMNS( { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 }, "__Month", [Value] )
    VAR __Table = ADDCOLUMNS( CROSSJOIN( __Years, __Months ), "__YearMonth", [__Year] * 100 + [__Month] )
    VAR __PreResult = COUNTROWS( FILTER( __Table, [__YearMonth] <= YEAR( __Today) * 100 + MONTH( __Today ) && [__YearMonth] >= YEAR( __Date ) * 100 + MONTH( __Date ) ) )
    VAR __Result = IF( DAY( __Today ) < DAY( __Date ), __PreResult - 1, __PreResult )
RETURN
    __Result

Alternatively, you could also always do this:

Measure =
    VAR __Today = TODAY()
    VAR __Date = MAX( 'Table'[Purchase Date] )
    VAR __PreResult = DATEDIFF( __Date , __Today, MONTH )
    VAR __Result = IF( DAY( __Today ) < DAY( __Date ), __PreResult - 1, __PreResult )
RETURN
    __Result

But where's the fun in that?