To **bleep** With TOTALMTD 07-25-2020 08:31 AM

[Greg_Deckler]

It's a little known fact that there are actually five, not four, Horsemen of the Apocalypse, their names being:

• Death
• Famine
• War
• Plague
• Insanity (AKA - DAX Time Intelligence Functions)

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 TOTALMTD, TOTALQTD and TOTALYTD. This also ends up covering DATESMTD, DATESQTD and DATESYTD. The code for these last three is basically all of the code up to and including the __DATESMTD, __DATESQTD and __DATESYTD variables.

Thus, one can see that the TOTALx functions are really just basically syntax sugar for a CALCULATE more or less because they could be written as TOTALMTD = CALCULATE(...,DATESMTD()) similar to all of those useless OPENINGBALANCE and CLOSINGBALANCE functions.

ToHellWithTOTALMTD = 
    VAR __Date = MAX('ProductInventory'[Date])
    VAR __Month = MONTH(__Date)
    VAR __Year = YEAR(__Date)
    VAR __StartOf = DATE(__Year,__Month,1)
    VAR __DATESMTD = FILTER(SELECTCOLUMNS(ALL('ProductInventory'),"Date",[Date]),[Date] >= __StartOf && [Date] <= __Date)
RETURN
    SUMX(FILTER(ALL('ProductInventory'),[Date] IN __DATESMTD),'ProductInventory'[UnitCost]*'ProductInventory'[UnitsBalance])


ToHellWithTOTALQTD = 
    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)
        )
    VAR __DATESQTD = FILTER(SELECTCOLUMNS(ALL('ProductInventory'),"Date",[Date]),[Date] >= __StartOf && [Date] <= __Date)
RETURN
    SUMX(FILTER(ALL('ProductInventory'),[Date] IN __DATESQTD),'ProductInventory'[UnitCost]*'ProductInventory'[UnitsBalance])


ToHellWithTOTALYTD = 
    VAR __Date = MAX('ProductInventory'[Date])
    VAR __StartOf = DATE(YEAR(__Date),1,1)
    VAR __DATESYTD = FILTER(SELECTCOLUMNS(ALL('ProductInventory'),"Date",[Date]),[Date] >= __StartOf && [Date] <= __Date)
RETURN
    SUMX(FILTER(ALL('ProductInventory'),[Date] IN __DATESYTD),'ProductInventory'[UnitCost]*'ProductInventory'[UnitsBalance])

eyJrIjoiNDhhYzc5ZGEtMmI5Zi00MjZjLThmMmUtMTZmZWFiYThhZDg2IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9