To **bleep** With FIRSTNONBLANK 07-27-2020 14:10 PM - last edited 07-27-2020 14:15 PM

[Greg_Deckler]

OK, these four I can only describe as weak. I mean, not quite certain why they are classified as Time "Intelligence" functions since you don't technically have to use them with anything date/time related. Although they still have the same crazy unintuitiveness, poor documentation and dumb restrictions as other time "intelligence" functions, so sure...they fit right in. These bad boys are basically just glorified MAX and MIN statements with filters for BLANK values. And the VALUE versions, those are just the Lookup Min/Max pattern. This covers FIRSTNONBLANK, LASTNONBLANK, FIRSTNONBLANKVALUE, LASTNONBLANKVALUE.

FIRSTNONBLANK = FIRSTNONBLANK('ProductInventory'[UnitProduct],[UnitProduct])
ToHellWithFIRSTNONBLANK = MINX(FILTER('ProductInventory',NOT(ISBLANK([UnitProduct]))),[UnitProduct])


LASTNONBLANK = LASTNONBLANK('ProductInventory'[UnitProduct],[UnitProduct])
ToHellWithLASTNONBLANK = MAXX(FILTER('ProductInventory',NOT(ISBLANK([UnitProduct]))),[UnitProduct])


FIRSTNONBLANKVALUE = FIRSTNONBLANKVALUE('ProductInventory'[Date],SUM(ProductInventory[UnitProduct]))
ToHellWithFIRSTNONBLANKVALUE = 
    VAR __Min = MINX(FILTER('ProductInventory',NOT(ISBLANK([UnitProduct]))),[Date])
RETURN
    MINX(FILTER('ProductInventory',[Date]=__Min),[UnitProduct])


LASTNONBLANKVALUE = LASTNONBLANKVALUE('ProductInventory'[Date],SUM(ProductInventory[UnitProduct]))
ToHellWithLASTNONBLANKVALUE = 
    VAR __Max = MAXX(FILTER('ProductInventory',NOT(ISBLANK([UnitProduct]))),[Date])
RETURN
    MAXX(FILTER('ProductInventory',[Date]=__Max),[UnitProduct])

If you want the full story, 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.

BTW, if you want to understand the VALUE versions of these functions, skip the documentation, it is amazingly unhelpful. Plus, I haven't found any good blogs on the subject either. Basically what these functions are doing is finding the first/last (read min/max) of the column passed in as the first parameter that has a non-blank value for the expression specified by the second argument. Then, they pass back the value of the expression passed in as the second argument at the first/last (still min/max) value of the column specified in the first parameter. In other words, Lookup Min/Max. 

eyJrIjoiMGEwMWZmZTEtY2ZmMC00NDA2LWE3YzUtZWY0MTJkYTQwZjhhIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9