Total Sum of Sale based on Specific period

[rajivsha]

I have this dataset where each account id has a different year (12 month period). For example CI-0018787 period is Apr to Mar while CI-0051871 has a period of Jan to Dec. I would like to calculate the total sale for each account id based on the respective period. Can anyone guide me how to do it?

Account ID	Sale Date	Sale	Start Date	End Date
CI-0018787	1-Jan-23	41,418,779	1-Apr-21	31-Mar-25
CI-0018787	1-Feb-23	36,704,248	1-Apr-21	31-Mar-25
CI-0018787	1-Mar-23	60,652,147	1-Apr-21	31-Mar-25
CI-0018787	1-Apr-23	84,240,787	1-Apr-21	31-Mar-25
CI-0018787	1-May-23	85,992,124	1-Apr-21	31-Mar-25
CI-0018787	1-Jun-23	84,459,178	1-Apr-21	31-Mar-25
CI-0018787	1-Jul-23	86,915,332	1-Apr-21	31-Mar-25
CI-0018787	1-Aug-23	87,654,280	1-Apr-21	31-Mar-25
CI-0018787	1-Sep-23	50,740,636	1-Apr-21	31-Mar-25
CI-0018787	1-Oct-23	47,635,071	1-Apr-21	31-Mar-25
CI-0018787	1-Nov-23	47,458,972	1-Apr-21	31-Mar-25
CI-0018787	1-Dec-23	47,479,272	1-Apr-21	31-Mar-25
CI-0018787	1-Jan-24	48,588,876	1-Apr-21	31-Mar-25
CI-0018787	1-Feb-24	46,267,732	1-Apr-21	31-Mar-25
CI-0018787	1-Mar-24	49,126,909	1-Apr-21	31-Mar-25
CI-0018787	1-Apr-24	43,892,482	1-Apr-21	31-Mar-25
CI-0018787	1-May-24	45,180,981	1-Apr-21	31-Mar-25
CI-0018787	1-Jun-24	47,906,942	1-Apr-21	31-Mar-25
CI-0018787	1-Jul-24	45,501,332	1-Apr-21	31-Mar-25
CI-0018787	1-Aug-24	48,350,946	1-Apr-21	31-Mar-25
CI-0018787	1-Sep-24	45,853,828	1-Apr-21	31-Mar-25
CI-0018787	1-Oct-24	45,997,016	1-Apr-21	31-Mar-25
CI-0018787	1-Nov-24	47,830,567	1-Apr-21	31-Mar-25
CI-0018787	1-Dec-24	46,168,283	1-Apr-21	31-Mar-25
CI-0018787	1-Jan-25	42,775,924	1-Apr-21	31-Mar-25
CI-0051871	1-Jan-23	680,528	1-Jan-25	31-Dec-29
CI-0051871	1-Feb-23	675,668	1-Jan-25	31-Dec-29
CI-0051871	1-Mar-23	739,044	1-Jan-25	31-Dec-29
CI-0051871	1-Apr-23	686,357	1-Jan-25	31-Dec-29
CI-0051871	1-May-23	749,620	1-Jan-25	31-Dec-29
CI-0051871	1-Jun-23	694,812	1-Jan-25	31-Dec-29
CI-0051871	1-Jul-23	706,585	1-Jan-25	31-Dec-29
CI-0051871	1-Aug-23	655,104	1-Jan-25	31-Dec-29
CI-0051871	1-Sep-23	595,067	1-Jan-25	31-Dec-29
CI-0051871	1-Oct-23	591,066	1-Jan-25	31-Dec-29
CI-0051871	1-Nov-23	463,261	1-Jan-25	31-Dec-29
CI-0051871	1-Dec-23	536,769	1-Jan-25	31-Dec-29
CI-0051871	1-Jan-24	825,967	1-Jan-25	31-Dec-29
CI-0051871	1-Feb-24	823,541	1-Jan-25	31-Dec-29
CI-0051871	1-Mar-24	852,400	1-Jan-25	31-Dec-29
CI-0051871	1-Apr-24	794,465	1-Jan-25	31-Dec-29
CI-0051871	1-May-24	769,737	1-Jan-25	31-Dec-29
CI-0051871	1-Jun-24	733,735	1-Jan-25	31-Dec-29
CI-0051871	1-Jul-24	723,032	1-Jan-25	31-Dec-29
CI-0051871	1-Aug-24	680,228	1-Jan-25	31-Dec-29
CI-0051871	1-Sep-24	631,130	1-Jan-25	31-Dec-29
CI-0051871	1-Oct-24	608,428	1-Jan-25	31-Dec-29
CI-0051871	1-Nov-24	578,634	1-Jan-25	31-Dec-29
CI-0051871	1-Dec-24	596,070	1-Jan-25	31-Dec-29
CI-0051871	1-Jan-25	698,670	1-Jan-25	31-Dec-29

[Anonymous]

Hi @rajivsha ,

I think you can create a Calendar table with Fiscal Year to help calculation.

Calendar = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"Fiscal Year",IF(MONTH([Date])>=4,YEAR([Date])+1,YEAR([Date])))

Relationship:

Then use [Fiscal Year] column to create the visual.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajivsha]

Thanks Rico but this doesn't meet my exact requirement. The fiscal year is different for each account id.

[BIswajit_Das · Accepted solution]

Hello @rajivsha 
Please follow the below attached file to get your required result.

Here in the file there's two calculated column created to calculated result column.
Try this out with your data to verify the calculation is working properly or not from your end and if you have any doubts message me.
Thanks & Regards ...

[rajivsha]

Thanks. This seems to be working for my dataset. I will check this with complete dataset I have.