YTD Calculation

[gauthamchilled]

Hi,

I would like to calculate YTD in the following way.

Ex: if my current selection is 2015 March, then the ytd calculation should be like below.

sales for Mar 15+Sales for Feb 15+Sales for Jan15+sales for Jan14+Sales for Feb14+Sales for Mar14

if I select jan 2015, then it should be

sales for jan 15+sales for jan 14 alone

How can I do this,

[sunny_talwar]

May be like this in your set analysis

{<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(Max(Date)))"} + {"$(='>=' & Date(AddYears(YearStart(Max(Date)), -1)) & '<=' & Date(AddYears(Max(Date), -1)))"}>}

[gauthamchilled]

Thanks sunny for the reply.

Small correction: if my current selection is 2015 then the formula should be like

(sales for Mar 15+Sales for Feb 15)/2+(Sales for Feb 15+Sales for Jan15)/2+(Sales of Dec15+Sales for Jan15)/2

(sales for Mar 14+Sales for Feb 14)/2+(Sales for Feb 14+Sales for Jan14)/2+(Sales of Dec14+Sales for Jan14)/2

in this case, how can I do?

[gauthamchilled]

sunindia‌ Can you help?

[sunny_talwar]

Working on it

[gauthamchilled]

The problem is I have to consider each month as (month+priormonth)/2..so for feb..(jan+feb)/2..

not sure how to calculate ytd in this case

[sunny_talwar]

May be like this:

(sales for Mar 15+Sales for Feb 15)/2

(Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date))) & '<=' & Date(MonthEnd(Max(Date))))"} Sales) +

Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -1))) & '<=' & Date(MonthEnd(AddMonths(Max(Date), -1))))"} Sales))/2

(Sales for Feb 15+Sales for Jan15)/2

(Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -1))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -1))))"} Sales) +

Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -2))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -2))))"} Sales))/2

(Sales of Dec14+Sales for Jan15)/2

(Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -2))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -2))))"} Sales) +

Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -3))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -3))))"} Sales))/2

See if the above helps.

Best,

Sunny

[sunny_talwar]

(sales for Mar 14+Sales for Feb 14)/2

(Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -12))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -12))))"} Sales) +

Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -13))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -13))))"} Sales))/2

(Sales for Feb 14+Sales for Jan14)/2

(Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -13))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -13))))"} Sales) +

Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -14))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -14))))"} Sales))/2

(Sales of Dec13+Sales for Jan14)/2

(Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -14))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -14))))"} Sales) +

Sum({<Date = {"$(='>=' & Date(MonthStart(AddMonths(Max(Date), -15))) & '<=' &Date(MonthEnd(AddMonths(Max(Date), -15))))"} Sales))/2

[gauthamchilled]

Sunny,

So I have to create the formula for each month? is there any other way to dynamically?

[gauthamchilled]

Anyone have idea how to calculate it dynamically?