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 Feb...
Need Help
Show and Tell
Tips and Tricks
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

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

    Unfortunately, this doesn't work.

     

    I need to have 6 cards in a row that show the MTD for each of the past 6 months.  I need to be able to see it on one page.

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

      Hi vbaker,

       

      I just made a testing and added a measure like this:

       

      TOTAL_VALUES_PREVIOUS_MONTHS =
VAR Date_Min=
    MIN ( Dim_date[Date] )
RETURN
    CALCULATE (
        SUM ( Files[Total] );
        Dim_date[Date] = Date_Min
    )

      Then add this measure to your cards with the relative filters grabbing the last 6 , 5 , 4, ... months see details below:

       

      On the green part make the last 6, 5, 4, 3, 2, 1 .

       

      Should work.

       

      Regards,

      MFelix

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

        The left column is the correct MTD, while the right column is utilizing the formula you shared.

         

        I don't know if I am missing something, or not, but it doesn't appear to give me the results I am looking for.

         

         

         

         

        Here is how I will eventually have it laid out, once I can get the formula squared away.