To **bleep** With OPENINGBALANCE 07-24-2020 12:20 PM

[Greg_Deckler]

So, similar to how I recreated all of those Excel functions, recently I have been on a kick unwiding the insanity that is the DAX Time Intelligence Functions. Sure, I started that a long time ago but might as well get specific. This all started with To **bleep** With STARTOFQUARTER.

Here we cover the OPENINGBALANCEMONTH, OPENINGBALANCEQUARTER, OPENINGBALANCEYEAR, CLOSINGBALANCEMONTH, CLOSINGBALANCEQUARTER and CLOSINGBALANCEYEAR. These six functions are true gems because even WITHIN the time "intelligence" functions they are completely and utterly useless. They are so useless in fact that I won't even bother explaining the traps you can fall into when using them. To demonstrate their sheer mind-numbing uselessness, the DAX example for OPENINGBALANCEQUARTER is given as:

Measure = OPENINGBALANCEQUARTER(SUMX(ProductInventory,ProductInventory[UnitCost]*ProductInventory[UnitsBalance]),DateTime[DateKey])

Which is exactly equivalent to:

ToHellWithOPENINGBALANCEQUARTER 2 = CALCULATE(SUMX('ProductInventory',ProductInventory[UnitCost]*ProductInventory[UnitsBalance]),STARTOFQUARTER(ProductInventory[Date]))

Disturbing. So basically these functions are the equivalent of using a CALCULATE and another time "intelligence" function. Thus, we find ourselves once again thinking...why? But that's nothing new when it comes to DAX's time "intelligence" functions.

Anyway, here they are sans any time "intelligence" functions at all:

ToHellWithOPENINGBALANCEMONTH = 
    VAR __Date = MAX('ProductInventory'[Date])
    VAR __Month = MONTH(__Date)
    VAR __Year = YEAR(__Date)
    VAR __StartOf = DATE(__Year,__Month,1)
RETURN
    SUMX(FILTER(ALL('ProductInventory'),[Date] = __StartOf),'ProductInventory'[UnitCost]*'ProductInventory'[UnitsBalance])


ToHellWithOPENINGBALANCEQUARTER = 
    VAR __Date = MAX('ProductInventory'[Date])
    VAR __Month = MONTH(__Date)
    VAR __Year = YEAR(__Date)
    VAR __StartOf =
        SWITCH(TRUE(),
            __Month <= 3,DATE(__Year,1,1),
            __Month <= 6,DATE(__Year,4,1),
            __Month <= 9,DATE(__Year,7,1),
            DATE(__Year,10,1)
        )
RETURN
    SUMX(FILTER(ALL('ProductInventory'),[Date] = __StartOf),'ProductInventory'[UnitCost]*'ProductInventory'[UnitsBalance])


ToHellWithOPENINGBALANCEYEAR = 
    VAR __Date = MAX('ProductInventory'[Date])
    VAR __StartOf = DATE(YEAR(__Date),1,1)
RETURN
    SUMX(FILTER(ALL('ProductInventory'),[Date] = __StartOf),'ProductInventory'[UnitCost]*'ProductInventory'[UnitsBalance])


ToHellWithCLOSINGBALANCEMONTH = 
    VAR __Date = MAX('ProductInventory'[Date])
    VAR __EndOf = EOMONTH(__Date,0)
RETURN
    SUMX(FILTER(ALL('ProductInventory'),[Date] = __EndOf),'ProductInventory'[UnitCost]*'ProductInventory'[UnitsBalance])


ToHellWithCLOSINGBALANCEQUARTER = 
    VAR __Date = MAX('ProductInventory'[Date])
    VAR __Month = MONTH(__Date)
    VAR __Year = YEAR(__Date)
    VAR __EndOf =
        SWITCH(TRUE(),
            __Month <= 3,DATE(__Year,3,31),
            __Month <= 6,DATE(__Year,6,30),
            __Month <= 9,DATE(__Year,9,30),
            DATE(__Year,12,31)
        )
RETURN
    SUMX(FILTER(ALL('ProductInventory'),[Date] = __EndOf),'ProductInventory'[UnitCost]*'ProductInventory'[UnitsBalance])


ToHellWithCLOSINGBALANCEYEAR = 
    VAR __Date = MAX('ProductInventory'[Date])
    VAR __EndOf = DATE(YEAR(__Date),12,31)
RETURN
    SUMX(FILTER(ALL('ProductInventory'),[Date] = __EndOf),'ProductInventory'[UnitCost]*'ProductInventory'[UnitsBalance])

eyJrIjoiMmZmNDRmOTctOWU0Ny00NTc3LWI4ZjktZmJjNGJjMTZkMjFlIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9