Current Financial Year & Previous Financial Year

[vardhancse]

Hi,

I have one field called [Financial Year]

data for that field was:

FY15

FY16

FY17

Based on selection of Financial Year field, bar chart should display the current year months and previous year months field.

Could any one please let me know the condition for the same.

Tried as below in expression:

Previous Year months:

=Sum({<Month=,Year= {$(= max(Year-1))}>}[Total Cost (actual)])

Current Year Months:

=Sum({<Month=,Year= {$(=max(Year))}>}[Total Cost (actual)])

But no luck in getting as required.

[kaushiknsolanki]

Hi,

You need to convert your financial year into number first and then the given expression will work.

Try this.

Load [Financial Year],

        Year(Makedate('20'&right([Financial Year],2)) as Year,

         ..

From Xyz;

Regards,

Kaushik Solanki

[vardhancse]

Thank you for your reply.

Both Financial Year and Year fields are coming from a master calendar.

Both the expressions are giving same value in my bar chart.

could you please let me know any alternative for the same.

[Anil_Babu_Samineni]

As Required means, Data points showing wrong you mean to say

May be missing Single Colon

Previous Year months:

=Sum({<Year= {'$(= max(Year-1))'}>}[Total Cost (actual)])

Current Year Months:

=Sum({<Year= {'$(=max(Year))'}>}[Total Cost (actual)])

If not, You may share your application to look in

kaushik.solanki‌ What if, Year has 1900,1901,----1999 

[vardhancse]

Can Please let me know any option to get max of Financial Year

so that based on Financial Year selection bar chart should be displayed.

Current Year:

=Sum({<Month=,[Financial Year]={'FY17'}>}[Total Cost (actual)])

Previous Year:

=Sum({<Month=,[Financial Year]={'FY16'}>}[Total Cost (actual)])

Instead of static I want to make it a dynamic based on Financial Year selection

[kaushiknsolanki]

If Year is in 1900 and if that value is correct then it will work.

But what I have seen is if you use date function on date then sometimes it gives dates in 1900 etc format.

In that case it may give wrong values.

Please check that.

Regards,

Kaushik Solanki

[vardhancse]

Hi If i am selection Financial Year = FY17

Then Year will be both 2016 & 2017

I am converting calendar into Fiscal calendar and so.

Our financial year starts from Oct '16 and end at Sep 17

[kaushiknsolanki]

Hi If possible share sample data along with expected output.

Regards,

Kaushik Solanki

[vardhancse]

Hi,

For time being used the below conditions (Static):

current year:

=if([Financial Year]='FY18',Sum({$<Month=, [Financial Year]={'FY18'}>} [Total Cost (actual)]),

if([Financial Year]='FY17',Sum({$<Month=, [Financial Year]={'FY17'}>} [Total Cost (actual)]),

if([Financial Year]='FY16',Sum({$<Month=, [Financial Year]={'FY16'}>} [Total Cost (actual)]),

if([Financial Year]='FY15',Sum({$<Month=, [Financial Year]={'FY15'}>} [Total Cost (actual)])))))

Previous Year:

=if([Financial Year]='FY18',Sum({$<Month=, [Financial Year]={'FY17'}>} [Total Cost (actual)]),

if([Financial Year]='FY17',Sum({$<Month=, [Financial Year]={'FY16'}>} [Total Cost (actual)]),

if([Financial Year]='FY16',Sum({$<Month=, [Financial Year]={'FY15'}>} [Total Cost (actual)]),

if([Financial Year]='FY15',Sum({$<Month=, [Financial Year]={'FY14'}>} [Total Cost (actual)])))))

for time being issue was resolve, but looking for a dynamic kind of thing like based on Financial Year selection the months total amount should be displayed.

[Anil_Babu_Samineni]

Does Financial Year is a field having values are FY14,15,16,17 and then so on? Confirm me. Really better offer if you provide sample to look on it