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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
christina4444
Helper I
Helper I

Calculating MTD - Ignoring Page Filter (Date)

Hi Community! 

 

I am looking to see if anyone could help me with a dax formula to ignore a Page date filter.

 

The outcome would be, no matter what page date filter is set, the formula would still result in sales MTD.

 

Example:

 

MTD Total Sales =

TOTALMTD( [total sales], ALL(calendar[date]))

 

Thank you so much!
Christina 🙂 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi @christina4444 

Do you want to show MTD sales as at "today" as returned by TODAY() function?

 

I would typically write something like this:

MTD Total Sales =
CALCULATE (
    [total sales],
    DATESMTD ( 'calendar'[date] = TODAY () )
)
    

As long as your calendar table is marked as a date table, this should work fine.

 

Please post back if you were looking for something different or this doesn't work for you.

 

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

4 REPLIES 4
OwenAuger
Super User
Super User

Hi @christina4444 

Do you want to show MTD sales as at "today" as returned by TODAY() function?

 

I would typically write something like this:

MTD Total Sales =
CALCULATE (
    [total sales],
    DATESMTD ( 'calendar'[date] = TODAY () )
)
    

As long as your calendar table is marked as a date table, this should work fine.

 

Please post back if you were looking for something different or this doesn't work for you.

 

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi @OwenAuger ,

 

I'm not sure why, but recently this formula stopped working.  

 

This is what it shows:

 

christina_chapm_0-1696093129876.png

When I add the formula to the table - the table only shows column headers, but the data is blank.  Any ideas? 

 

Thank you!! 

Christina

Hi @christina4444 ,

 

That's interesting - here a some suggestions to debug:

  1. Depending on your timezone, is TODAY() now in October? (It is for me but might not be for you, and if the report is published, TODAY() will use UTC timezone). If so, it's possible that the measure is filtering on October but that there is no data available yet in October.
    You can create a separate measure Current Date = TODAY() and put it on a card or other visual to check.
  2. In the MTD Invoices measure, you could try replacing TODAY() with recent dates leading up to today, and see if there's a specific date when it stops working.
  3. Does your Calendar table include dates >= TODAY()? In any date table, I would suggest including full calendar years that span your data. In this case, is the max 'Calendar'[Date] at least 31-Dec-2023?
  4. Double-check that 'Calendar' is marked as a Date table.

One other minor point (probably not related to the current issue): 'Calendar' should be enclosed in single quotes since it's a reserved word (a function name). You can get away with it sometimes but I would do this as a safeguard:

 

MTD Invoices =
CALCULATE (
    [Total Invoices],
    DATESMTD ( 'Calendar'[Date] = TODAY () )
)

 

 

Let me know if any of the above help! 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

It worked!  Thank you so much, @OwenAuger !

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.