Sum of Amount by previous Fiscal year and current Fiscal year

[Vinothsusai]

Hi,

I have a table below. My previous fiscal year starts at 1/7/2018(July) to 30/6/2019 and current fiscal year starts at 1/7/2019(July) to 30/6/2020.

Date              Amount

6/1/2018	300
7/1/2018	100
7/10/2018	100
8/5/2018	100
9/11/2018	200
12/12/2018	200
1/1/2019	100
2/13/2019	500
3/14/2019	800
5/18/2019	900
6/20/2019	100
7/22/2019	200
8/13/2019	100
9/11/2019	199
12/12/2019	200

I want to create a measure for previous fiscal year that has to be sum only previous fiscal year records like below

Date              Amount

6/1/2018	300
7/1/2018	100
7/10/2018	100
8/5/2018	100
9/11/2018	200
12/12/2018	200
1/1/2019	100
2/13/2019	500
3/14/2019	800
5/18/2019	900
6/20/2019	100

Total  =   3400

For current fiscal year that has to be sum only current fiscal year records

Date              Amount

7/22/2019	200
8/13/2019	100
9/11/2019	199
12/12/2019	200

Total = 699

Could you please advise.

Thanks

Vinoth SUSAINATHAN

[Anonymous · Accepted solution]

Hi Vinoth - 

You can try the following measures in your table (assuming the fiscal year values are hardcoded and will not change)

For previous fiscal year:
"

prevFiscalYearTotal =

VAR fiscalYearStart = DATE(2018,7,1)

VAR fiscalYearEnd = DATE(2019,6,30)

RETURN

CALCULATE(

SUM('TableName'[Amount]),

FILTER(

'TableName',

'TableName'[DateAmount] >= fiscalYearStart &&

'TableName'[DateAmount] <= fiscalYearEnd

)

)

"

And for the current year you can change the date values in the variable above.

If the above helped you, please select this as solution.

Regards

[Vinothsusai]

Hi,

Thank you very much for your reply. The date values should not be hard coded values. This has to be dynamically calculates the current and previous fiscal year. Could you please advise.

Thanks

Vinoth S

[Vinothsusai]

Hi,

It works great. I just replaced the datavalues as Year(Today()) -1 for previous fiscal year.

prevFiscalYearTotal =

VAR fiscalYearStart = DATE(YEAR(TODAY())-1,7,1)

VAR fiscalYearEnd = DATE(YEAR(TODAY()),6,30)

RETURN

CALCULATE(

SUM('Amount calculation'[Amount]),

FILTER(

'Amount calculation',

'Amount calculation'[Date] >= fiscalYearStart &&

'Amount calculation'[Date] <= fiscalYearEnd

)

)

Thank you

Vinoth SUSAINATHAN

[Anonymous]

Awesome, thanks much for that point you mentioned, and thanks for accepting the solution.

All the best.