MTD returns sum of total instead of month only

[KevinCP]

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

Thanks in advance. 

[Ashish_Mathur]

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.

[v-ljerr-msft · Accepted solution]

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

[KevinCP]

This worked. Thanks!