YTD calculation

[harsha384]

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

[Joe_Mako · Accepted solution]

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

[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

[harsha384]

Thanks Joe. This solution works for me.