YTD, QTD

[Anonymous]

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!

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

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

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]

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]

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! 

[v-ljerr-msft]

Hi @Anonymous,

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

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]

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 ()
    )
)

[Ashish_Mathur]

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.

[sgsukumaran]

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

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