cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Having Trouble with calculating sum based on Fiscal Year

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Having Trouble with calculating sum based on Fiscal Year

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

View solution in original post

2 REPLIES 2
Highlighted
Microsoft
Microsoft

Re: Having Trouble with calculating sum based on Fiscal Year

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

View solution in original post

Highlighted
Helper III
Helper III

Re: Having Trouble with calculating sum based on Fiscal Year

 

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? 

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors