Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
sburtonprovenit
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
Greg_Deckler
Super User
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?

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
sburtonprovenit
Frequent Visitor

Thanks that worked

Greg_Deckler
Super User
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?

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.