Forum Discussion

vbaker's avatar
vbaker
Icon for Helper I rankHelper I
8 years ago

Month to Date -X

I have a formula get Month to Date, but I want to create a measure that allows me to do MTD -X.  Essentially, I need to have a card that will display the MTD for January, another card for MTD for February, etc.

 

Here is my formula for MTD:

MTD Amount = CALCULATE(SUM('Table'[Amount]), FILTER('Table',DAY('Table'[Date])<=DAY(TODAY()+1)))

Need Help
Show and Tell
Tips and Tricks

22 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    8 years ago

    Hi vbaker,

     

    Try to do the following formula:

     

    MTD Amount = CALCULATE(TOTALMTD(SUM('Table'[Amount]), 'Table'[Date]))

    Then just add the month filter on each card.

     

    Regards,

    MFelix

    • vbaker's avatar
      vbaker
      Icon for Helper I rankHelper I
      8 years ago

      While this solution would work.  I need it to be a rolling update, as this is live data that will update on a regular basis.  I am trying to avoid having to go in and change the filter each and every month.  Essentially, I want to set it and let it run on a continual basis.

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        8 years ago

        Hi vbaker,

         

        On the card add the date and then choose the Relative Date Filtering option in the last 1 calendar months.

         

         

        This will give you the always the last month based on today.

         

        Regards,

        MFelix