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
christina_chapm
Frequent Visitor

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 @christina_chapm 

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 @christina_chapm 

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 @christina_chapm ,

 

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.