Fiscal Year Accumalation

[adarsh_a]

Hi All,

        I have to calculate cumulative sum based on fiscal year i.e from April of last year to march of current year

Eg:

         

  The Output should be like this:

I'm able to get cumulative sum based on calendar year using the expression

aggr(RangeSum(Above(Sum(Sale),0,RowNo())),Year,New_Month)

How to do it for fiscal Year?

[Gysbert_Wassenaar · Accepted solution]

In this case you can use a quick-and-dirty like this one:

Data:

LOAD Date,

     Sale,

     if(month(Date)=4,Sale, rangesum(Sale, peek(CumulativeSale)))as CumulativeSale

FROM

Fiscal_Year_Accumulation.xlsx

(ooxml, embedded labels, table is Sheet1);

A more robust solution would be to create a Fiscal Year calendar (see this blog post: Fiscal Year) and perhaps create an AsOf table for easy accumulations (see Calculating rolling n-period totals, averages or other aggregations).

[adarsh_a]

Thanks for the solution, it is working