Forum Discussion

Icon for Helper I rank

Helper 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...

Tips and Tricks

Icon for Super User rank

Super User

8 years ago

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

Icon for Helper I rank

Helper 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.

MFelix
Super User
8 years ago
Can you share both measures please just to check the dax.

Regards,
MFelix
- vbaker
  Helper I
  8 years ago
  MFelix
  Here are my two DAX formulas being used.
  
  MTD Amount = CALCULATE(SUM('Table'[Amount]), FILTER('Table',DAY('Table'[Date])<=DAY(TODAY()+1)))
  
  MTD Amount-0 = VAR Date_Min = MIN('Table'[Date]) RETURN CALCULATE(SUM('Table'[Amount]), 'Table'[Date] = Date_Min)
  
  Here is the DAX I am using to do previous month. This could work with month, however not every month is the same number of days.
  
  Previous Day-1 =
  CALCULATE(SUM('Table'[Amount]),DATESBETWEEN('Table'[Date],LASTDATE('Table'[Date])-1,LASTDATE('Table'[Date])-1))
  - MFelix
    Super User
    8 years ago
    Hi vbaker,
    
    Try to change your MTD Amount-0 to use the MTD Amount variable so basicly you are using the same calculation but when you add the context of previous 6 months it will get the min date for last 6 months.
    
    MTD Amount-0 = VAR Date_Min = MIN('Table'[Date]) RETURN CALCULATE([MTD Amount], 'Table'[Date] = Date_Min)
    
    Regarding your question about the number of days you need to choose calendar months that way the calculations will get full month.
    
    Please try the above change and tell me if it worked, other wise if it's possible please share a sample file so that I can check the calculations and give a best answer.
    
    Regards,
    MFelix

Related content