To bleep With NEXTDAY (Lone Biker of the Apocalypse)

Greg_Deckler — Fri, 24 Jul 2020 21:16:59 GMT

"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:

Create a date table with all possible dates for which you might want to find the next day
Use said date table as the parameter for the NEXTDAY function
Ensure that a relationship exists between your fact table and your date table
Ensure that the cardinality of the relationship is such that it is Single from your fact table to your date table
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

Re: To bleep With NEXTDAY (Lone Biker of the Apocalypse)

Anonymous — Tue, 11 May 2021 00:05:10 GMT

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!

topic Re: To **bleep** With NEXTDAY (Lone Biker of the Apocalypse) in Quick Measures Gallery

To **bleep** With NEXTDAY (Lone Biker of the Apocalypse)

Re: To **bleep** With NEXTDAY (Lone Biker of the Apocalypse)

topic Re: To bleep With NEXTDAY (Lone Biker of the Apocalypse) in Quick Measures Gallery

To bleep With NEXTDAY (Lone Biker of the Apocalypse)

Re: To bleep With NEXTDAY (Lone Biker of the Apocalypse)