Need to calculate current and prev year YTD

[harsha384]

Hi All,

I need to calculate current year YTD and Prev year YTD assuming that the YTD is calculated starting from Jan'01.

Now the tricky part of the issue is that the months are in columnar format.Please find the attached excel.

1. First I need to calculate current year YTD, so if current month is Oct-17 then YTD should be sum of Oct-17 to Jan-17.

2. Similarly, I need to calculate YTD for 2016 for exact same number of months which will be Oct-16 to Jan-16

The process should be dynamically progressing with respect to current month.

Please help!

Thanks

Harsh

[NicoleJohnson · Accepted solution]

Hi @harsha384 - try something like the attached!

By Transposing your columns into one column and doing a little data prep, you can calculate which month you're currently in based on max date and then summarize the values for any dates that include months that are <= to the current month. You can then Summarize and Cross-Tab the results to calculate the YTD values for this year & prior year. 

Hope that helps!

Cheers,

NJ

[NicoleJohnson]

@harsha384 - per your message, another example, although again I will add that this is not going to be as efficient, and will require manual updating for every new month and new year. 

Take a look at both - once you open the original one I sent (after changing the version), I think you'll find that the Transpose option is your best bet!

Either way, hope these help...

Cheers,

NJ

[harsha384]

Hi @NicoleJohnson,

Thank you for your support. So my query is answered that the issue cannot be resolved without transposing the months into single column.

Appreciate your help on the same!

Cheers

Harsh