Need to calculate current and prev year YTD

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

YTD.xlsx

Output

Dynamic Processing

Accepted answers

NicoleJohnson

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,

YTDExample.yxmd

All comments

NicoleJohnson

Hi @harsha384 - try something like the attached!

Hope that helps!

Cheers,

YTDExample.yxmd

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,

YTDExample2.yxmd

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703