4 Replies Latest reply on Nov 15, 2017 6:33 PM by Shinichiro Murakami

# Month-To-Date, Prior Year-To-Date, & Month over Month Change Calculations

Looking for assistance in a few different calculations, specifically in the attached dummy data workbook, in the "High Level Dashboard" tab.

- I'd like the number that appears above "Latest Month" to be a Current Month-To-Date (MTD) figure.

- For the number above MoM change, I'd like that to reflect a comparison between the current month-to-date versus the same period last month.  For instance, November 1st - November 10th this month compared to October 1st to October 10th last month.

- For the reference line that vertically intersects the horizontal colored bar, I'd like that to reflect the previous Year-To-Date (not all of 2016.  So if we're looking at the YTD 2017 horizontal bar as of October 15th, I'd like the reference line to reflect the YTD as of October 15, 2016.

Ideally when I use the date filter in the top right, the calculations would dynamically change the previous YTD reference lines, the Latest Month numbers, and the Month over Month Change figures.

It would be great to have a workbook with these solutions included in it, and then offer a brief explanation of how each was established.

Thanks,

Marc

• ###### 1. Re: Month-To-Date, Prior Year-To-Date, & Month over Month Change Calculations

Hi Marc,

Please attach the data in an extract to the workbook format .tbwx

Regards,

Norbert

• ###### 2. Re: Month-To-Date, Prior Year-To-Date, & Month over Month Change Calculations

Apologies.  Please see the attached workbook as an extract.

• ###### 3. Re: Month-To-Date, Prior Year-To-Date, & Month over Month Change Calculations

HI Marc

I am not sure how you want to handle M to Date on line chart.

Because the latest month does not have full month, it is not showing trend correctly.

How do you want to handle this?

Line chart is always showing Full month and all others are start(month/year) to date?

I cannot find easy answer on this with considering the combination of whole logic and function limitations.

Thanks,

Shin

• ###### 4. Re: Month-To-Date, Prior Year-To-Date, & Month over Month Change Calculations

Anyways, up to now.

Share Date filter across whole sheet with linking "Date" field on both data source.

Change the date filter to context to capture the latest date with LOD correctly.

==> This does not allow to draw the correct Line chart for the latest month.

Thanks,

Shin

2 of 2 people found this helpful