To **bleep** With NEXTDAY (Lone Biker of the Apocalypse) 07-23-2020 08:17 AM - last edited 07-24-2020 14:16 PM

[Greg_Deckler]

"Last night I had a dream. I drifted off thinking about happiness, birth and new life. But now I was haunted by a vision of DAX time intelligence functions. They...were...horrible..."

So, similar to how I recreated all of those Excel functions, my next fun project is unwiding the insanity that are the DAX Time Intelligence Functions. Sure, I started that a long time ago but might as well get specific. BTW, this all started with To **bleep** With STARTOFQUARTER. Here I cover NEXTDAY, PREVIOUSDAY, NEXTMONTH, PREVIOUSMONTH, NEXTQUARTER, PREVIOUSQUARTER, NEXTYEAR, PREVIOUSYEAR

ToHellWithNEXTDAY = ( MAX('Data'[Date]) + 1)

ToHellWithPREVIOUSDAY = MAX('Data'[Date]) - 1

Fail 1 is when you might, quite naturally, think that you should feed the date you are trying to find the start of the month for in as a parameter. Fail.

Fail 2 is when you pass in the parameter of a date column in a date table but you don't have a relationship with that particular date table that is in context. Fail.

When it actually works, you have to have a relationship with your date table in context AND pass in the parameter of a date column in that date table. Oh, except that nice blank row that you get... Fail.

Turns out, in order to get NEXTDAY, and most other time intelligence functions, to work you must adhere to the following strict guidelines:

1. Create a date table with all possible dates for which you might want to find the next day
2. Use said date table as the parameter for the NEXTDAY function
3. Ensure that a relationship exists between your fact table and your date table
4. Ensure that the cardinality of the relationship is such that it is Single from your fact table to your date table
5. Stand on your head and spin around 3 times while clicking your heels together and singing "I'm Too Sexy" by Right Said Fred

And somehow this is better than just + 1 or - 1?

But perhaps the biggest problem with all of these functions is:

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Anyway, here are the table forms of all of these functions. Just replace TODAY() with whatever date you want. And all of these will work in DirectQuery mode and with RLS.

ToHellWithNEXTDAY = { TODAY() + 1 }


ToHellWithPREVIOUSDAY = { TODAY() - 1 }


ToHellWithNEXTMONTH = 
    VAR __Date = TODAY()
    VAR __NextMonth = EOMONTH(__Date,1)
    VAR __Month = MONTH(__NextMonth)
    VAR __Year = YEAR(__NextMonth)
RETURN
    CALENDAR(DATE(__Year,__Month,1),__NextMonth)


ToHellWithPREVIOUSMONTH = 
    VAR __Date = TODAY()
    VAR __NextMonth = EOMONTH(__Date,-1)
    VAR __Month = MONTH(__NextMonth)
    VAR __Year = YEAR(__NextMonth)
RETURN
    CALENDAR(DATE(__Year,__Month,1),__NextMonth)


ToHellWithNEXTQUARTER = 
    VAR __Date = TODAY()
    VAR __Quarter = QUARTER(__Date)
    VAR __Year = SWITCH(__Quarter,4,YEAR(__Date) + 1,YEAR(__Date))
    VAR __MonthStart = SWITCH(__Quarter,1,4,2,7,3,10,1)
    VAR __MonthEnd = SWITCH(__Quarter,1,6,2,9,3,12,3)
    VAR __Day = SWITCH(__Quarter,1,30,2,30,3,31,31)
RETURN
    CALENDAR(DATE(__Year,__MonthStart,1),DATE(__Year,__MonthEnd,__Day))


ToHellWithPREVIOUSQUARTER = 
    VAR __Date = TODAY()
    VAR __Quarter = QUARTER(__Date)
    VAR __Year = SWITCH(__Quarter,1,YEAR(__Date) - 1,YEAR(__Date))
    VAR __MonthStart = SWITCH(__Quarter,1,10,2,1,3,4,7)
    VAR __MonthEnd = SWITCH(__Quarter,1,12,2,3,3,6,9)
    VAR __Day = SWITCH(__Quarter,1,31,2,31,3,30,30)
RETURN
    CALENDAR(DATE(__Year,__MonthStart,1),DATE(__Year,__MonthEnd,__Day))


ToHellWithNEXTYEAR = 
    VAR __Date = TODAY()
    VAR __Year = YEAR(__Date) + 1
RETURN
    CALENDAR(DATE(__Year,1,1),DATE(__Year,12,31))


ToHellWithPREVIOUSYEAR = 
    VAR __Date = TODAY()
    VAR __Year = YEAR(__Date) - 1
RETURN
    CALENDAR(DATE(__Year,1,1),DATE(__Year,12,31))

Sure, these use CALENDAR but if you want to filter a calendar table, knock yourself out, you can do that too.

eyJrIjoiNDJlMWI2OTYtY2Y5MC00OGZiLTllMjQtNjdlZjMyODQ1YTU5IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[Anonymous]

Does explain why I get blank on previousday using a date table? Using 

{ TODAY() - 1 } works like a charm.  So annoying. To hell with with previousday function!