cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Months between today and a date in a table

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.

1 ACCEPTED SOLUTION
Super User

@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?

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
2 REPLIES 2
Frequent Visitor

Thanks that worked

Super User

@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?

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...