cancel
Showing results for
Did you mean:
KevinCP Frequent Visitor

## MTD returns sum of total instead of month only

Struggling to understand why the below doesn't work? It gives me the sum of all carts instead of only the current month. Is there an explanation for this?

MTD Carts = CALCULATE(SUM(table_name[Carts]),FILTER(table_name,MONTH(table_name[date].[MonthNo]=MONTH(TODAY()))))

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor

## Re: MTD returns sum of total instead of month only

Hi @KevinCP,

If I understand you correctly, the formula below should work in your scenario. ```MTDCarts =
CALCULATE (
SUM ( table_name[Carts] ),
FILTER (
table_name,
YEAR ( table_name[date] ) = YEAR ( TODAY () )
&& MONTH ( table_name[date] ) = MONTH ( TODAY () )
)
)```

Regards

3 REPLIES 3
v-ljerr-msft Super Contributor

## Re: MTD returns sum of total instead of month only

Hi @KevinCP,

If I understand you correctly, the formula below should work in your scenario. ```MTDCarts =
CALCULATE (
SUM ( table_name[Carts] ),
FILTER (
table_name,
YEAR ( table_name[date] ) = YEAR ( TODAY () )
&& MONTH ( table_name[date] ) = MONTH ( TODAY () )
)
)```

Regards Super User

## Re: MTD returns sum of total instead of month only

Hi,

Here's my suggestion:

1. Create a calendar table with dates that span those in your base data table
2. Extract the month name from the Date in a calculated column using the =FORMAT(Calendar[Date]) formula
3. Create a relatiosnhip from the Date column in Table_name to the Date column in the Calendar table
4. In your visual drag the Month from the Calendar Table
5. In the visual filter section, drag date from the Calendar table and use a relative filter for current month
6. Create this measure

=SUM(table_name[Carts])

hope this helps.

Highlighted
KevinCP Frequent Visitor

## Re: MTD returns sum of total instead of month only

This worked. Thanks!