Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
OK, hopefully we have reached the low point of this project because, I mean, what exactly was the thought process behind creating functions that are essentially MIN and MAX but with a whole host of restrictions? I mean, just use MIN and MAX for crying out loud...
If you are new to this project or this is the first post you are stumbling across, this is part of a project to unwind the insanity that is DAX's Time "Intelligence" functions. It is kindof similar to how I recreated all of those Excel functions. Sure, I started my war against time intelligence functions a long time ago but figured I'd just go all out at this point. This current project all started with To **bleep** With STARTOFQUARTER. Here we cover FIRSTDATE and LASTDATE which are mind bogglingly stupid DAX functions. I mean even more mind bogglingly stupid than STARTOFYEAR and ENDOFYEAR and I didn't think it would get worse than that.
Anyway, FIRSTDATE and LASTDATE are exactly what they say they are, they return the first and last dates in context (read min and max). But, bonus, they come with a whole host of caveats and restrictions including:
The dates argument can be any of the following:
Constraints on Boolean expressions are described in the topic, CALCULATE function.
When the current context is a single date, the date returned by the FIRSTDATE and LASTDATE functions will be equal.
The Return value is a table that contains a single column and single value. Therefore, this function can be used as an argument to any function that requires a table in its arguments. Also, the returned value can be used whenever a date value is required.
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
Also, oddly, the functions return a table of dates. A table mind you. Even though you are finding the "first" date or the "last" date, you need a table returned? There's only one first or last folks. At least that's how it generally works prior to millenials showing up and everyone getting a trophy. Perhaps we should call these the DAX millenial functions... Everyone wins.
So, as I stated, these functions are crazy dumb. You can just use MIN and MAX:
ToHellWithFIRSTDATE = MIN('Calendar'[Date])
ToHellWithLASTDATE = MAX('Calendar'[Date])
There are even millenial equivalent functions that return an entire table of firsts and lasts:
ToHellWithFIRSTDATE = { MIN('Calendar'[Date]) }
ToHellWithLASTDATE = { MAX('Calendar'[Date]) }
And, you can use these with DirectQuery and Row Level Security and with no other crazy restrictions. And if you want to use a table expression, you can use MAXX and MINX.
So, I have to wonder what the thinking was of creating functions that are nothing other than more restrictive versions of MIN and MAX. I really, really want to know. Was it really just so you didn't have to switch between using MIN/MINX and MAX/MAXX? And somehow that justifies the additional restrictions???
eyJrIjoiMjkzMDAyNzktZGY0ZS00ZmI3LWE3ZmItMTBiNjQ4NjI5ZTMzIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9