Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

YTD, QTD

Hello all,

 

I have a question that is probably simple, but for two days I have tried so many different things and can't find a solution. I understand the concept of MTD/YTD/QTD totals. However, at my job they like to see each of those values based on the last month completed, or last quarter completed for the QTD total. It's December so it would look like this. 

 

MTD - Month Total for November

YTD - Jan1 - November 30

QTD - Total of last completed quarter. 

 

Below are the measures I have based on true MTD, YTD, QTD and they work perfectly:

 

MTD Units Actual = CALCULATE([Total Units Actual],DATESMTD(CalendarTable[Date]))

YTD Units Actual = CALCULATE([Total Units Actual],DATESYTD(CalendarTable[Date]))

QTD Units Actual = CALCULATE([Total Units Actual],DATESQTD(CalendarTable[Date])) 

 

I do have a CalendarTable. So, my question is how can I use these or some other DAX expression to accomplish what I described above.  Thanks in advance!

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi Anonymous,

     

    Sorry for my misunderstanding. Could you try the formulas below to see if it works? :smileyhappy:

    Previous Month Units =
CALCULATE (
    [Total Units Actual],
    FILTER (
        ALL(CalendarTable),
        YEAR ( CalendarTable[Date] ) = YEAR ( TODAY () )
            && MONTH ( CalendarTable[Date] ) = MONTH ( TODAY () ) -1
    )
)

    YTD Units =
CALCULATE (
    [Total Units Actual],
    FILTER (
        ALL(CalendarTable),
        YEAR ( CalendarTable[Date] ) = YEAR ( TODAY () )
            && MONTH ( CalendarTable[Date] ) <= MONTH ( TODAY () ) -1
    )
)

    QTD Units =
CALCULATE (
    [Total Units Actual],
    FILTER (
        ALL ( CalendarTable ),
        ENDOFQUARTER ( CalendarTable[Date] )
            = STARTOFQUARTER ( TODAY () ) - 1
    )
)

     

    Regards

7 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    Try the formulas below, which should give your expected result. :smileyhappy:

    MTD Units Actual =
CALCULATE (
    [Total Units Actual],
    FILTER (
        CalendarTable,
        YEAR ( CalendarTable[Date] ) = YEAR ( TODAY () )
            && MONTH ( CalendarTable[Date] ) = MONTH ( TODAY () )
            && CalendarTable[Date] <= TODAY ()
    )
)

    YTD Units Actual =
CALCULATE (
    [Total Units Actual],
    FILTER (
        CalendarTable,
        YEAR ( CalendarTable[Date] ) = YEAR ( TODAY () )
            && CalendarTable[Date] <= TODAY ()
    )
)

    QTD Units Actual =
CALCULATE (
    [Total Units Actual],
    FILTER (
        CalendarTable,
        YEAR ( CalendarTable[Date] ) = YEAR ( TODAY () )
            && CalendarTable[Date] >= STARTOFQUARTER ( TODAY () )
            && CalendarTable[Date] <= TODAY ()
    )
)

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      These measures work great, except they are still showing the true MTD, YTD, and QTD. Any thoughts on what I need to do so the prior month totals? I tried the below and still didn't work

       

      Thanks in advance! 

      MTD Units Actual =
CALCULATE (
    [Total Units Actual],
    FILTER (
        CalendarTable,
        YEAR ( CalendarTable[Date] ) = YEAR ( TODAY () )
            && MONTH ( CalendarTable[Date] ) = MONTH ( TODAY ()-1)
            && CalendarTable[Date] <= TODAY ()
    )
)
  • sgsukumaran's avatar
    sgsukumaran
    Resolver II
    8 years ago

    You need to first calculate if the month is completed and then do MTD

     

    ValueCalc = if(TODAY()>=EOMONTH(VALUES(Table1[DateRef]);0);EXPTRUE;EXPRFALSE)

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    All you need to do is drag Year to the filter/slicer and select 2017.  Likewise, drag Month to the filter/slicer and select November.

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    Sorry for my misunderstanding. Could you try the formulas below to see if it works? :smileyhappy:

    Previous Month Units =
CALCULATE (
    [Total Units Actual],
    FILTER (
        ALL(CalendarTable),
        YEAR ( CalendarTable[Date] ) = YEAR ( TODAY () )
            && MONTH ( CalendarTable[Date] ) = MONTH ( TODAY () ) -1
    )
)

    YTD Units =
CALCULATE (
    [Total Units Actual],
    FILTER (
        ALL(CalendarTable),
        YEAR ( CalendarTable[Date] ) = YEAR ( TODAY () )
            && MONTH ( CalendarTable[Date] ) <= MONTH ( TODAY () ) -1
    )
)

    QTD Units =
CALCULATE (
    [Total Units Actual],
    FILTER (
        ALL ( CalendarTable ),
        ENDOFQUARTER ( CalendarTable[Date] )
            = STARTOFQUARTER ( TODAY () ) - 1
    )
)

     

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks again for you replies. It worked perfectly. Any idea how I might refine this just a bit, so that if it's January, then show the last month as December of prior year? 

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago
        MTD Units - Actual Prior Month = 
IF (
    MONTH ( TODAY () ) = 1,
    CALCULATE (
        [Total Units - Actual],
        FILTER (
            DateTable,
            YEAR ( DateTable[Date] )
                = YEAR ( TODAY () ) - 1
                && MONTH ( DateTable[Date] )
                    = MONTH ( TODAY () ) -1
        )
    ),
    CALCULATE (
        [Total Units - Actual],
        FILTER (
            DateTable,
            YEAR ( DateTable[Date] ) = YEAR ( TODAY () )
                && MONTH ( DateTable[Date] )
                    = MONTH ( TODAY () ) - 1
        )
    )
)

        Nevermind, I figured it out! I just used the IF funtion inside the measure!