YTD calculation

Hi All,

I need some help for YTD calculation. The condition should be that if the year doesn't have January month data, the YTD of that year should be zero.

Please find the attached file. The required YTD output is calculated in the sheet.

Thanks

Harsh

YTD.xlsx

Input

Output

Common Use Cases

Transformation

Dynamic Processing

Accepted answers

Joe_Mako

How about the attached?

- Filter to remove the blank lines from your example file, may not be necessary with your real data if there are no blank records

- Multi-Field Formula to convert the Date field's data type, may not be necessary with your real data if it is already a data type

- Formula to pull out the Year from the Date

- Summarize to group by Year and return the Min Date

- Formula to convert the Min Date in the Min Month as a number

- Join by Year so every record has the Min Month number for its Year

- Sort by Date to ensure the correct sort

- Multi-Row Formula to perform a running sum if the Min Month is 1, zero if it is not 1, and it is set to Group by Year so it restarts the running sum for each Year

YTD.yxmd

All comments

Joe_Mako

How about the attached?

- Filter to remove the blank lines from your example file, may not be necessary with your real data if there are no blank records

- Multi-Field Formula to convert the Date field's data type, may not be necessary with your real data if it is already a data type

- Formula to pull out the Year from the Date

- Summarize to group by Year and return the Min Date

- Formula to convert the Min Date in the Min Month as a number

- Join by Year so every record has the Min Month number for its Year

- Sort by Date to ensure the correct sort

- Multi-Row Formula to perform a running sum if the Min Month is 1, zero if it is not 1, and it is set to Group by Year so it restarts the running sum for each Year

YTD.yxmd

jdunkerley79

I like @Joe_Mako solution but here is a variant approach.

- Use a Running Total tool to make the sum of the month as we go along (makes assumption that Date is ordered, add a sort if not)

- Use a formula tool to make up the Jan date (01-01-<Year>)

- Use a find and replace to self join on the new field back to the old (Find and Replace doesnt change input order and does a left outer join )

- Finally use a formula tool to make YTD

YTDCalc.yxmd

harsha384

Thanks Joe. This solution works for me.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703