Forum Discussion

TomEnns's avatar
TomEnns
Icon for Helper I rankHelper I
4 years ago
Solved

Calculating QTD is not working as expected. (DAX)

Still learning the ropes in Dax. I need to Calculate the current and next quarter totals for a number of capacity hours in a table. Here is what I have: ResourceCapacity Table Includes these field...
  • TomEnns's avatar
    TomEnns
    4 years ago

    Thankyou, This got me to where I needed to be. For reference here is the final formula:

    Capacity - This Quarter = CALCULATE(
        VAR LastDayOfQuarter =  EOMONTH ( TODAY(), 2 - MOD ( MONTH ( TODAY() ) - 1, 3 ) )
        VAR FirstDayOfQuarter = DATE ( YEAR ( TODAY() ), ROUNDUP ( DIVIDE ( MONTH ( TODAY() ),3 ),0 ) *3 -2, 1)
        VAR results = [Capacity]((DATESBETWEEN(DateDim[Date],FirstDayOfQuarter,LastDayOfQuarter)))
        RETURN
        results
    )
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Does this work too?

     

    Capacity - This Quarter =
    VAR ThisYear = YEAR ( TODAY () )
    VAR ThisQtr  = QUARTER ( TODAY () )
    RETURN
        CALCULATE (
            [Capacity],
            FILTER (
                ALL ( DateDim ),
                YEAR ( DateDim[Date] ) = ThisYear &&
                QUARTER ( DateDim[Date] ) = ThisQtr
            )
        )