YTD calculation without dates column

[#1]

Hello All,

I am writing this to get help from the community on calculating YTD, MTD, QTD etc. without having a date field.

Let me elaborate the issue, I have month number, year, day of month etc stored in separate columns but i wont be able to concatenate and make that into a date column as i have days numbered more than 31 days.i am following Fiscal calendar and the number of days in a particular month could vary between 1 to 31 for some months and 1 to 35 days for the rest.

Any help is highly appreciated.

Thanks.

[sunny_talwar]

Would you be able to share a sample of how your data looks and the kind of output you are looking for

[#3 · Accepted solution]

The sample calendar looks like the attached one. i am trying to calculate YTD, MTD etc. based on the Fiscal calendar. And the Fiscal start and end dates for each month will vary for every year.

[#4]

Hello,

Did anyone get a chance to look at the attached excel book? I do not know how to get more people to look at this

[sunny_talwar]

I don't understand the data pasted in your Excel sheet. Can you elaborate what is the input and what is the expected output here

[#6]

The attached excel is a sample of the calendar which should be used across all the applications in my environment.

Column, "PeriodDT" is the actual date on our regular calendar, but the calculations have to be done considering the Fiscal dates, Fiscal Months and Fiscal Quarters from the excel.

Eg: Assume today is 1 Apr 2016, then the MTD should be calculated from the beginning of the Fiscal Start of Month which would be 28-feb-2016 (On the regular calendar, refer the PeriodDT column in the excel). Similarly, the YTD, QTD calculations are to be calculated based on the FISCAL calendar and not on the regular calendar.

Please let me know if it is possible.