To **bleep** With TOTALMTD 12-08-2023 13:14 PM

[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

[Magellan]

@Greg_Deckler . Hello, can this approach be used in a line graph where the x-axis is by month name only and also where the "to-date" portion of the YTD is dynamic and ends on the date based on year, month name and week slicer values selected? Essentially, the line graph would show all the month names of the year and only show the amount for each month up to the date that the year, month name and week slicers values are based off of.

[gdrum]

@Greg_Deckler hi. very helpful for me with direct query. So why not use Today() instead of MAX for YTD Ending. 

VAR __Date = Date(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))

[Greg_Deckler]

@gdrum Perfectly acceptable to use TODAY as well. Just depends on the specifics of the data model and requirements.