To **bleep** With DATEADD 07-29-2020 07:06 AM - last edited 07-29-2020 18:51 PM

[Greg_Deckler]

OK, I believe this one should put the final nail in the coffin of Time "Intelligence" functions in DAX. This post covers DATEADD, SAMEPERIODLASTYEAR, DATESINPERIOD and PARALLELPERIOD. There is perhaps a parallel universe where I might have some grudging respect for these functions. This is not that universe. Sure, there is some tricky date manipulation stuff going on, especially with quarters but at the end of the day, these functions are all just glorified FILTER functions. Poorly documented, unintuitive FILTER functions with, frankly, some relatively bizarre behavior at times. I suppose that I should just be happy that these functions actually do something relatively difficult at times versus being sugar syntax for MAX, MIN or CALCULATE.

Honestly though, there is no way to predict the behavior of these functions with the documentation provided. In fact, there is a solid chance that if you *think* you know how these functions behave in all circumstances that you are sadly mistaken. These things were miserable to recreate quite honestly. Hours and hours of testing boundary cases. I *think* I have these pretty much 100% correct but I cannot be absolutely certain. So anyway, I have separate versions of these functions for all of the intervals that each supports. Maybe at some point I will combine them into a single function but that was too much work after spending all night last night pounding these out. Anyway, download the PBIX, they are all there along with the actual time "intelligence" equivalents for comparison.

Here is a representative sample of the functions included:

ToHellWithDATEADD DAY = 
    VAR __NumIntervals = 10
    VAR __MaxDate = MAXX('Calendar',[Date])
    VAR __MinDate = MINX('Calendar',[Date])
    VAR __Start = 
        SWITCH(TRUE(),
            __NumIntervals = 0 || __NumIntervals < 0,__MinDate,
            __MinDate + __NumIntervals
        )
    VAR __End = 
        SWITCH(TRUE(),
            __NumIntervals = 0 || __NumIntervals > 0,__MaxDate,
            __MaxDate + __NumIntervals
        )
RETURN
    FILTER('Calendar',[Date] >= __Start && [Date] <= __End)


ToHellWithSAMEPERIODLASTYEAR = 
    VAR __NumIntervals = -1
    VAR __MaxDate = MAXX('Calendar',[Date])
    VAR __MinDate = MINX('Calendar',[Date])
    VAR __Start = __MinDate
    VAR __End = 
        SWITCH(TRUE(),
            __NumIntervals = 0 || __NumIntervals > 0,__MaxDate,
            IF(
                MONTH(__MaxDate) = 2 && DAY(__MaxDate) > 28,
                EOMONTH(__MaxDate,__NumIntervals*12),
                DATE(YEAR(__MaxDate) + __NumIntervals, MONTH(__MaxDate), DAY(__MaxDate))
            )
        )
RETURN
    FILTER('Calendar', [Date] >= __Start && [Date] <= __End)


ToHellWithDATESINPERIOD MONTH = 
    VAR __NumIntervals = 2
    VAR __StartDate = DATE(2020,11,3)
    VAR __MaxDate = MAXX('Calendar',[Date])
    VAR __MinDate = MINX('Calendar',[Date])
    VAR __StartMonth = MONTH(EOMONTH(__StartDate,(MOD(ABS(__NumIntervals),12) * SIGN(__NumIntervals))))
    VAR __StartDay = DAY(__StartDate)
    VAR __StartYear = YEAR(EOMONTH(__StartDate,__NumIntervals))
    VAR __Start = 
        SWITCH(TRUE(),
            __StartDate < __MinDate,__MinDate,
            __NumIntervals = 0 || __NumIntervals < 0,DATE(__StartYear,__StartMonth,__StartDay) + 1,
            __StartDate
        )
    VAR __EndMonth = MONTH(EOMONTH(__Start,(MOD(ABS(__NumIntervals),12) * SIGN(__NumIntervals))))
    VAR __EndDay = DAY(__Start)
    VAR __EndYear = YEAR(EOMONTH(__Start,__NumIntervals))
        VAR __End = 
        SWITCH(TRUE(),
            __NumIntervals = 0 || __NumIntervals > 0,DATE(__EndYear,__EndMonth,__EndDay) - 1,
            __StartDate
        )
    VAR __Table = FILTER('Calendar',[Date] >= __Start && [Date] <= __End)
RETURN
    FILTER(__Table, [Date] <= __MaxDate)


ToHellWithPARALLELPERIOD QUARTER = 
    VAR __NumIntervals = 2
    VAR __MaxDate = DATE(2019,10,21)
    VAR __MinDate = DATE(2019,6,10)
    VAR __StartMonth1 = MONTH(EOMONTH(__MinDate,__NumIntervals*3+1))
    VAR __StartMonth = 
        SWITCH(TRUE(),
            __StartMonth1 < 4,1,
            __StartMonth1 < 7,4,
            __StartMonth1 < 11,7,
            10
        )
    VAR __StartYear = YEAR(EOMONTH(__MinDate,__NumIntervals*3+1))
    VAR __Start = DATE(__StartYear,__StartMonth,1)
    VAR __EndMonth1 = MONTH(EOMONTH(__MaxDate,__NumIntervals*3))
    VAR __EndMonth = 
        SWITCH(TRUE(),
            __EndMonth1 < 4,3,
            __EndMonth1 < 7,6,
            __EndMonth1 < 11,9,
            12
        )
    VAR __EndYear = YEAR(EOMONTH(__MaxDate,__NumIntervals*3))
    VAR __End = EOMONTH(DATE(__EndYear,__EndMonth,1),0)
RETURN
    FILTER('Calendar',[Date] >= __Start && [Date] <= __End && [Date] >= MIN('Calendar'[Date]) && [Date] <= MAX('Calendar'[Date]))

If you want the full story about my crusade, similar to how I recreated all of those Excel functions, my next fun project has been unwiding the insanity that are the DAX Time Intelligence Functions. Sure, I started that a long time ago but might as well get specific and create an alternative DAX calculation for each time "intelligence" function. BTW, this all started with To **bleep** With STARTOFQUARTER.

eyJrIjoiMmJmZjI2M2MtYTBmZC00MmZlLWIxOGMtOThhNWY1YTY3MTgwIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9