cancel
Showing results for
Did you mean:

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

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
Employee

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

7 REPLIES 7
Employee

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

Employee

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

```MTD Units Actual =
CALCULATE (
[Total Units Actual],
FILTER (
CalendarTable,
YEAR ( CalendarTable[Date] ) = YEAR ( TODAY () )
&& MONTH ( CalendarTable[Date] ) = MONTH ( TODAY ()-1)
&& CalendarTable[Date] <= TODAY ()
)
)```
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
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)

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors