cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
KevinCP Frequent Visitor
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()))))

 

Thanks in advance. 

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
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. Smiley Happy

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
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. Smiley Happy

MTDCarts =
CALCULATE (
    SUM ( table_name[Carts] ),
    FILTER (
        table_name,
        YEAR ( table_name[date] ) = YEAR ( TODAY () )
            && MONTH ( table_name[date] ) = MONTH ( TODAY () )
    )
)

 

Regards

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

KevinCP Frequent Visitor
Frequent Visitor

Re: MTD returns sum of total instead of month only

This worked. Thanks!