Month to Date -X

MFelix

Here is a sample of how I am doing everything currently in my pbix file

https://www.dropbox.com/s/xip5w0l9iq04njc/Sample%20Billing%20Data.pbix?dl=0

Hi vbaker,

Create a calculated column and a measure with the formulas below:

CALCULATED FORMULAMonth_Start = STARTOFMONTH(TABLE_NAME[Date])

MEASURE
MTD Amount-0 =
VAR Date_Min =
    MIN ( TABLE_NAME[Month_Start] )
RETURN
    CALCULATE ( [MTD Amount]; TABLE_NAME[Month_Start] = Date_Min )

Then just add the measure to your card visuals and use the relative date to get the latest month.

Check the image below as you can see the measure calculate with context the same value as current month so when you add it to a visual and filter the first date to be 6 months before it shows the value of November. This only as 6 months of dates but should work on a continuous calendar so next month should appear April value in previous month and so on.

Check the PBIX file (We transfer link only available for 7 days).

As a best practice I would create a calendar table and make a relationship to the fact table.

Regards,

MFelix

MFelix

That works, however it doesn't allow me to run it through the other measures I have to get the outlier distance, or am I missing something?

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