1 2 Previous Next 17 Replies Latest reply on Nov 1, 2016 10:24 PM by sneha simha

# Mtd formula

M using this formula for calculating MTD TY

and

[Date]<=TODAY()-1 then [Net Value] end

But my problem is i have data From April 2016 till august 2016.Can anyone help me in fetching Data for August month TY and Last year also with YTD TY and YTD LY.

Thanks and Regards

Sneha

• ###### 1. Re: Mtd formula

Hi Sneha

Find below my approach as reference and stored in attached workbook version 9.3

1 of 1 people found this helpful
• ###### 2. Re: Mtd formula

Hi,

YTD formula:

IF [Order Date] <= TODAY()

AND DATEDIFF('year',[Order Date],Today())= 0 THEN [Sales] END

MTD Formula:

IF [Order Date] <= TODAY()

AND DATEDIFF('month',[Order Date],Today())= 0 THEN [Sales] END.

Try with the above formula.

• ###### 3. Re: Mtd formula

Hi,

No MTD didnt work i dont have data for Oct 2016 at all, i have it till Aug 2016 so trying to retrieve value for August 2016. Please help.

YTD is working.

Regards

Sneha

• ###### 4. Re: Mtd formula

Hi Norbert

Thanks a ton YTD is working but m unable to get MTD and also YTD value should be a summation from April till August.Kind;y help.

Regards

Sneha

• ###### 5. Re: Mtd formula

Hi Sneha,

Find my approach as reference below

Replace TODAY() by parameter with all available dates. If you select a date is will give you the sales of that month

1 of 1 people found this helpful
• ###### 6. Re: Mtd formula

Thanks Norbert

But YTD is not giving me the summation of values From April till August and how do i calculate MTD last year and YTD last year.Kindly oblige and help me on this too.

Regards

Sneha

• ###### 7. Re: Mtd formula

Hi Sneha,

Find below my approach as reference and stored in workbook version 9.3

• ###### 8. Re: Mtd formula

Hi Norbert

Can you please explain in detail.

Regards

Sneha

• ###### 9. Re: Mtd formula

Hi Sneha

As an example: Last 5 month

if [Order Date]<=[Order Date Parameter 2] and DATEDIFF('month',[Order Date],[Order Date Parameter 2])<=5

and DATEDIFF('year',[Order Date],[Order Date Parameter 2])=0

then [Sales] END

If order date is smaller than selected date in parameter and the difference between current month and month is less then 5 and difference between the year of order date and selected date in parameter is 0 then give the sales

1 of 1 people found this helpful
• ###### 10. Re: Mtd formula

Hi Norbert

Sorry for troubling, i will paste the values, you can see that my MTD TY ,MTD LY sales are the same.Kindly help me resolve this issue as m just unable to arrive my desired output cause of these formulas.

• ###### 11. Re: Mtd formula

Good morning Sneha,

I have other assignments this morning I will come back to you later.

Have a nice day.

• ###### 12. Re: Mtd formula

Very Good Morning Norbert.

Thank you will wait for you.Have a great day ahead.

• ###### 13. Re: Mtd formula

Hi sneha,

Please provide the formulas you are using which can help better understand the issue

Thanks

• ###### 14. Re: Mtd formula

See this one.

You have to adapt to retrieve Aug/2016 since you don't have.

1 2 Previous Next