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