4 Replies Latest reply on Nov 4, 2015 5:37 AM by Daniel Vincent

# YTD for each month

Hi,

I've done YTD as following :

Create a parameter with [date_choice]

Create calculated fieldYTD :

IF [Book Date] <= [date_choice] AND DATEDIFF('year',[Book Date],date_choice])= 0 THEN [Actual] END

This work fine if I want to display only a YTD of a month - I only pick in the column Year(Book Date)

BUT as soon as I divide it into month, it display then the MTD (cauz the start date is the current Month)

How I can do in the formula that the start date is 01/01/xxx if the current year ? In order to see each YTD of each month

• ###### 1. Re: YTD for each month

Hi Med,

My understanding of your task is that, if you select a date with [date_choice], you want to get a total of your measures from the first day of that month to that day.

If this is correct, try this:

IF [Book Date] <= [date_choice] AND [Book Date] >= DATETRUNC('month', [date_choice]) THEN [Actual] END

If this does not solve the problem, please attach a sample workbook. As much as I can suggest ways to get a MTD, the way you need to do it will depend on how you are arranging the data in your workbook.

Best regards.

1 of 1 people found this helpful
• ###### 2. Re: YTD for each month

Hello Med,

I built an example workbook that discusses what you are looking for. It has multiple parameters that all the user to swap between YTD, QTD and MTD based on a parameter date.

Current vs Previous Period to Date Comparison

I hope this helps

Regards,

Rody

• ###### 3. Re: YTD for each month

Sorry to reopen this but I'm faced again to the same issue.

I can have a YTD of a defined date but I can't display at the same time the YTD corresponding for each month . Because with YTD formula, as soon as I put month into the column shelf, it's doing the MTD automatically.

To illustrate this, my data are like this :

 Name Value Book date A 10 31/01/2015 A 10 30/02/2015 A 10 30/02/2015 A 10 31/03/2015 I can display Name YTD (02/15) A 30 But i wanted to show : January (ytd) February (ytd) March(ytd) 10 30 40
• ###### 4. Re: YTD for each month

A little clarification needed.

Do you want the YTD value to show in each month to be the same?   Do you want a running YTD total for each month?

It would greatly help us to post a package workbook example with what you expect for the months.