MTD YOY Based on Fiscal Calendar

MD2050

Hello Everyone-

I am trying to pull the MTD values Year over Year for comparison and i am not been able to come with a logic. For example what we are looking for is to pull June month's value for current year vs. 2017 based on the fiscal calendar for which we have another input file. The month is decided by the run date i.e,if the report is run today based on today's date it will determine what PERIOD_NAME data to display. So today being 6/12 it should pull JUN-18 and based on that period the program should pull the same period_name data corresponding to last year which is JUN-17 ; given the fact that June is still on going so JUN-18 values will change everyday until the JUNE's period is finished .

Please find in attached my work along with the data file, i am probably making it more complicated then it should be and would like to know if there is a better way of achieving it .

**Please note- for some reason the Period_NUM values are showing as dates like 2018-06-07 which in reality are JUN-18 (MMM-YY) format when i pull the data from Oracle Table.(please see snippet attached)

Thank you .

period_name.docx

Comparison_v1.yxmd

Workflow

Tips and Tricks

Parse

Preparation

Accepted answers

danrh

I think the issue is the "and" in your statement:

([INVOICE_DATE]>= [PY_Min_Inv_Date] and [INVOICE_DATE]<=[PY_Max_Inv_Date])
and
tonumber(DateTimeParse([INVOICE_DATE],"%Y"))==ToNumber(DateTimeParse(DateTimeToday(),"%Y"))

The first half is checking whether or not INVOICE_DATE is within the appropriate days for the prior year, while the second half checks whether the year of the INVOICE_DATE is the same as the year for today. These two will never be both true --- a single date can't be both last year and this year.

You need two separate filters, one with the first half of this logic and the other with the second half.

Then you Summarize each separate data stream and join them back together, either using a Join or an Append.

I also picked at how YTD would tie into the previous workflow I posted --- see the attached for that approach.

solution.yxmd

All comments

MD2050

Adding the Data file separately as i was not able to add multiple files in single message. Please find in attach the data file.

Thank you.

data.xlsx

danrh

Something like this?

solution.yxmd

MD2050

GM @danrh-

Thank you very much for your help.

CY MTD period will be decided from the Invoice date which will be in terms of the financial calendar . For example , if the Invoice date would have fell on the 5/27/2018 then based on this date and referencing to the attached data set file the invoice should fall in Jun-18 period although the month is May . So the comparison will be Jun-18 vs Jun-17 (regardless of what dates are covered in Jun-17 bucket. Last year's period will be decided on what period we are in current year so if we are in July it should pull July for last year) , this is what i was trying to achieve by adding a datetimeadd() tool but i couldn't configure it to go 1 period back based on the current period unlike how we usually do for year, month or days.

I apologize for the confusion.

Thank you ,

period_name.yxdb

danrh

I'm having a little trouble wrapping my head around it. Could you post some sample data and your expected result?

MD2050

I am sorry for the confusion . Please find in attached the excel file in which i created a Pivot table showing what we are expecting.

You will notice a date filter on top of each pivot table which shows what date range we have to compare which is based on the PERIOD_NAME.

for example- today's date is 6/13/2018 so based on the table below it will fall under JUN-18 period (5/27/18-6/30/18) now based on JUN-18 we want to compare last year's JUN-17 period for comparison. This report will be generating daily so on 7/1/2018 the month will change and so will be the PERIOD_NAME which will be JUL-18 then we will be comparing to JUL-17. So based on the invoice date the program should refer to the table below determine which period it falls into and then based on that period pull sales corresponding to last year for the same period. (JUN-18 vs. JUN-17, AUG-18 vs. AUG-17,..DEC-18 vs. DEC-17)

I hope i was able to explain. Thank you very much.

data.xlsx

danrh

I did a couple workarounds to cope with some irregularities in your data, so let me know if the attached works for your actual data.

solution.yxmd

MD2050

Hello @danrh-

Thank you very much for the solution , it worked out perfectly. I had to tweak it a little to adjust it to PERIOD_NAME format , following is what i tweaked in the filter tool because period names are MMM-YY format.

DateTimeFormat(DateTimeToday(),'%b') = Left([PERIOD_NAME],3)
AND
[PERIOD_YEAR] >= DateTimeYear(DateTimeToday())-1

Can you please help me directing what am i doing wrong in the YTD logic ? please see my work attached .

In extension of the MTD CY vs PY , i want to ADD 2 columns for CY_YTD vs. PY_YTD , meaning if the report is run today then it should sum all the sales from 01/01/2018 through 06/12/2018 under CY_YTD bucket and then put all the sales from 01/01/2017 through 06/12/2017 in PY_YTD bucket. (Data is same what i attached in the earlier post.)

i am using below logic but not getting anywhere.

([INVOICE_DATE]>= [PY_Min_Inv_Date] and [INVOICE_DATE]<=[PY_Max_Inv_Date])
and
tonumber(DateTimeParse([INVOICE_DATE],"%Y"))==ToNumber(DateTimeParse(DateTimeToday(),"%Y"))

i really appreciate your help. Thank you very much.

Comparison_YTD.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703