Having Trouble with calculating sum based on Fiscal Year

[siddhantk989]

Hi I am creating a measure where I am calculating sum of sales based on current fiscal year ( note - fiscal year starts from October) using the following function

Current sales = CALCUALTE(SUM(Sales),FILTER(DataSet, DataSet[Fiscal Year] = MAX(Fiscal Year)))

Now I am plotting a line chart using the above measure which shows be sales based on months for the Fiscal Year. The graph should show me sales from October to May as that all is part of Fiscal Year 2017 but I don't know why it is picking up values from June to September from Year 2016.

Instead if I use 2017 instead of MAX(Fiscal Year) in the above measure I am getting the correct values, but I don't want  to use the static value.

Any suggestion on why I am getting 2016 values from June - September?

[v-huizhn-msft · Accepted solution]

Hi @siddhantk989,

In your resource table, if there is June to September for 2016 year? If there is, it will display in your line chart when you select Month-Year as axis level. For your issue, please modify your formula as follows.

Current sales = CALCUALTE(SUM([Sales]),FILTER(DataSet, DataSet[Fiscal Year] = CALCULATE(MAX([Fiscal Year]),ALL(DataSet))))

Thanks,
Angelia

[siddhantk989]

Hi @v-huizhn-msft,

 The solution you provided worked. But I still don’t understand the wired issue about showing data for FY 2016 from June to September in 2017 Line graph.

I am using just the Fiscal Month on my x axis and because I have data from Oct 2017 - May 2017 (Fiscal Year) so that is coming in the proper way but because I don’t have data from June 2017 - September 2017. It is taking the data from FY 2016.

Is it because I am using 

DataSet[Fiscal Year] = MAX([Fiscal Year])

and instead of taking MAX(Year) as a whole it is taking MAX(Year) based on each month?