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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

 

Thanks in advance. 

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

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

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-ljerr-msft
Employee
Employee

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

This worked. Thanks! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.