Calculating MTD - Ignoring Page Filter (Date)

[christina4444]

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 🙂 

[OwenAuger · Accepted solution]

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

[christina4444]

Hi @OwenAuger ,

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

This is what it shows:

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

Thank you!! 

Christina

[OwenAuger]

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! 🙂

[christina4444]

It worked!  Thank you so much, @OwenAuger !