Month to Date -X

I need to know how this month, month to date, relates to the previous 6 months, month to date.  What my report needs to display is within the Dashboard page.  This Dashboard page is at the Day level, and management wants it at the Month level now.

So, I need to measure the quartiles, the outlier number, the outlier distance.

If I could do something within the Previous Day measure, this would be able to filter everything through appropriately. 

Hi vbaker,

Just add this measure to your data and then use it on your cards.

MTD Amount-0 var =
VAR Date_Max =
    MAX ( TABLE_NAME[Month_Start] )
RETURN
    CALCULATE ( [MTD Amount]; TABLE_NAME[Month_Start] = Date_Max )
        - [MTD Amount-0]

This measure is equal to the previous one but your are always getting the Maximum month in this case is March 2018, then jus subtract to your previous measure.

See here the PBIX file.

Regards,

MFelix

MFelix

I think we are getting there, however something just seems to bit off.

I have included a page with sample output and the entire breakdown of the formulas being used

Sample PBIX File

Hi vbaker,

Sorry for making this question nut not really sure what you are trying to achieve you have all the formulas, and the result however not sure what is the expected result you want to achieve.

Again just wnat to help you the best way I can and still not wrapping my head around the final outcome you are suppose to get.

Regards,

MFelix

MFelix

What I am needing to do is compare the month to date for the month compared to the month to dates for all the previous months in history.

So, if I am looking at April 17, 2018, I need to compare the current sum MTD against the 1-17 day sum for all months previously (2013-Present).  What the outlier distance tells me is that if the 1-17 date sum for this month is within the acceptable range versus the previous MTD in time.

The catch is, I need to display last month's sum MTD (March 1-17) versus the 1-17 day sum for months previously before that (excluding April, since it is after March).  This needs to be for the past 6-7 months.

For outlier distance, and acceptable distance is between -3 and 3, so having a 30+ outlier distance is alarming.

When running the sample numbers through the formula, the outlier distance for MTD should be 0.54, not 40.22, which is how it is now.

Maybe I am doing something wrong and need to approach it differently?  I am open to any ideas.  Thank you for all your assistance.

Hi vbaker,

Sorry for insisting on this but there is something on the setup that I still not getting can you make an MTD example with number that I can check within the model you sent.

All the detailed calculations? Just for one month example March and February comparision.

You file is very complete but I'm lost within the measures within measures and can't get the number you want to have to calculate everything matching.

Again sorry for the question but just wnat to give you every help possible.

Regards,

MFelix

MFelix

I sent you a PM with my current document that I am using