Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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!

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

Hi @Anonymous,

 

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

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

View solution in original post

7 REPLIES 7
v-ljerr-msft
Employee
Employee

Hi @Anonymous,

 

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

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
Not applicable

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
Not applicable

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
Employee
Employee

Hi @Anonymous,

 

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

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
Not applicable

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
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
sgsukumaran
Resolver II
Resolver II

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

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.