Rolling DATEDIFF 06-10-2024 12:23 PM

[Greg_Deckler]

Look, let's not beat around the bush. The DATEDIFF function in DAX is a bit scuff. At best it is syntax sugar for subtraction. At worst, it thinks that December 31st, 2023 and January 1st, 2024 are 1 year apart. Not exactly helpful. Instead, these quick measures implement a rolling DATEDIFF. Day, Hour, Minute and Second versions are not exactly rolling but instead just use simple math versus black box magic. The Month, Quarter, Year, and Week versions are rolling measures.

ToHellWithDATEDIFF Month = 
    VAR __Date1 = [Today]
    VAR __Date2 = MAX( [Purchase Date] )
    VAR __MinDate = MIN( __Date1, __Date2 )
    VAR __MaxDate = MAX( __Date1, __Date2 )
    VAR __Multiplier = IF( __Date1 < __Date2, -1, 1 )
    VAR __Years = SELECTCOLUMNS( GENERATESERIES( YEAR( __MinDate ), YEAR( __MaxDate ), 1 ), "__Year", [Value] )
    VAR __Months = SELECTCOLUMNS( GENERATESERIES(1, 12, 1), "__Month", [Value] )
    VAR __Table = ADDCOLUMNS( CROSSJOIN( __Years, __Months ), "__Date", DATE( [__Year], [__Month], DAY( __Date1 ) ) )
    VAR __Table1 = FILTER( __Table, [__Date] >= __MinDate && [__Date] <= __MaxDate )
    VAR __PreResult = COUNTROWS( __Table1 )
    VAR __Result = IF( __Multiplier > 0, __PreResult - 1, __PreResult * __Multiplier )
RETURN
    __Result

ToHellWithDATEDIFF Quarter = 
    VAR __Date1 = [Today]
    VAR __Date2 = MAX([Purchase Date])
    VAR __MinDate = MIN( __Date1, __Date2 )
    VAR __MaxDate = MAX( __Date1, __Date2 )
    VAR __Multiplier = IF( __Date1 < __Date2, -1, 1 )
    VAR __Years = SELECTCOLUMNS( GENERATESERIES( YEAR( __MinDate ), YEAR( __MaxDate ), 1 ), "__Year", [Value] )
    VAR __Quarters = SELECTCOLUMNS( GENERATESERIES( 1, 4, 1 ), "__Quarter", [Value] )
    VAR __Table = ADDCOLUMNS( CROSSJOIN( __Years, __Quarters ), "__YearQuarter", [__Year] * 100 + [__Quarter] )
    VAR __Count = COUNTROWS( FILTER( __Table, [__YearQuarter] <= YEAR( __MaxDate ) * 100 + QUARTER( __MaxDate ) && [__YearQuarter] >= YEAR( __MinDate ) * 100 + QUARTER( __MinDate ) ) )
    VAR __RollingQuartersTable = 
        ADDCOLUMNS(
            GENERATESERIES( 1, __Count, 1 ),
            "__Date", 
                VAR __EOM = EOMONTH( __Date1, 3 * [Value] * __Multiplier * -1  )
                VAR __Result = DATE( YEAR( __EOM ), MONTH( __EOM ), DAY( __Date1 ) )
            RETURN
                __Result
        )
    VAR __Table1 = FILTER( __RollingQuartersTable, [__Date] >= __MinDate && [__Date] <= __MaxDate )
    VAR __PreResult = COUNTROWS( __Table1 )
    VAR __Result = IF( __Multiplier > 0, __PreResult, ( __PreResult + 1 ) * __Multiplier )
RETURN
    __Result + 0

ToHellWithDATEDIFF Year = 
    VAR __Date1 = [Today]
    VAR __Date2 = MAX([Purchase Date])
    VAR __MinDate = MIN( __Date1, __Date2 )
    VAR __MaxDate = MAX( __Date1, __Date2 )
    VAR __Multiplier = IF( __Date1 < __Date2, -1, 1 )
    VAR __Years = SELECTCOLUMNS( GENERATESERIES( YEAR( __MinDate ), YEAR( __MaxDate ), 1 ), "__Year", [Value] )
    VAR __Table = ADDCOLUMNS( __Years, "__Date", DATE( [__Year], MONTH( __Date1 ), DAY( __Date1 ) ) )
    VAR __Table1 = FILTER( __Table, [__Date] >= __MinDate && [__Date] <= __MaxDate )
    VAR __PreResult = COUNTROWS( __Table1 )
    VAR __Result = IF( __Multiplier > 0, __PreResult - 1, __PreResult * __Multiplier )
RETURN
    __Result

ToHellWithDATEDIFF Week = 
    VAR __Date1 = [Today]
    VAR __Date2 = MAX([Purchase Date])
    VAR __MinDate = MIN( __Date1, __Date2 )
    VAR __MaxDate = MAX( __Date1, __Date2 )
    VAR __Multiplier = IF( __Date1 < __Date2, -1, 1 )
    VAR __Years = SELECTCOLUMNS( GENERATESERIES( YEAR( __MinDate ), YEAR( __MaxDate ), 1 ), "__Year", [Value] )
    VAR __Weeks = SELECTCOLUMNS( GENERATESERIES( 1, 53, 1 ), "__Week", [Value] )
    VAR __Table = ADDCOLUMNS( CROSSJOIN( __Years, __Weeks ), "__YearQuarter", [__Year] * 100 + [__Week] )
    VAR __Count = COUNTROWS( FILTER( __Table, [__YearQuarter] <= YEAR( __MaxDate ) * 100 + WEEKNUM( __MaxDate ) && [__YearQuarter] >= YEAR( __MinDate ) * 100 + WEEKNUM( __MinDate ) ) )
    VAR __RollingWeeksTable = 
        ADDCOLUMNS(
            GENERATESERIES( 1, __Count, 1 ),
            "__Date", __Date1 + 7 * [Value] * __Multiplier * -1
        )
    VAR __Table1 = FILTER( __RollingWeeksTable, [__Date] >= __MinDate && [__Date] <= __MaxDate )
    VAR __PreResult = COUNTROWS( __Table1 )
    VAR __Result = IF( __Multiplier > 0, __PreResult, ( __PreResult + 1 ) * __Multiplier )
RETURN
    __Result + 0

ToHellWithDATEDIFF Day = TRUNC( ( [Today] - MAX( 'Table'[Purchase Date] ) ) * 1 )

ToHellWithDATEDIFF Hour = 
    VAR __Base = ( [Today] - MAX( 'Table2'[Purchase Date Time] ) ) * 1.
    VAR __Hours = __Base * 24
    VAR __Result = IF( __Hours > 0, ROUNDUP( __Hours, 0), TRUNC( __Hours ) )
RETURN
    __Result

ToHellWithDATEDIFF Minute = 
    VAR __Base = ( [Today] - MAX( 'Table2'[Purchase Date Time] ) ) * 1.
    VAR __Minutes = __Base * 24 * 60
    VAR __Result = IF( __Minutes > 0, ROUNDUP( __Minutes, 0), TRUNC( __Minutes ) )
RETURN
    __Result

ToHellWithDATEDIFF Second = 
    VAR __Base = ( [Today] - MAX( 'Table2'[Purchase Date Time] ) ) * 1.
    VAR __Seconds = __Base * 24 * 60 * 60
    VAR __Result = IF( __Seconds > 0, ROUNDUP( __Seconds, 0), TRUNC( __Seconds ) )
RETURN
    __Result

Learn more here in these videos:

eyJrIjoiNTcwZTVmMjMtNTFjYS00ZjM1LWI0YTEtOTBlZTYxOGU5Njk4IiwidCI6Ijg3NDlmOWI5LWYzMmQtNDdhMS1hMjI0LTM2OTQxOGFlMmY1MSJ9