Forum Discussion

christina4444's avatar
christina4444
Icon for Helper I rankHelper I
3 years ago
Solved

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 sti...
  • OwenAuger's avatar
    OwenAuger
    3 years ago

    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

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
3 years ago

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's avatar
    christina4444
    Icon for Helper I rankHelper I
    2 years ago

    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's avatar
      OwenAuger
      Icon for Super User rankSuper User
      2 years ago

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