cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AlexanderAT Regular Visitor
Regular Visitor

Showing 2 years of turnover but only this years budget in line and column chart

Hello

 

I want to show last year and this year's turnover by month in the same chart as this year's budget. The problem I'm having is that the budget line is a cluster of this year and last years budget. 

I tried to make a new measurement that should only include the budget for the present year:

                            ThisYearBudget = CALCULATE((Budget[Budget FBP]), YEAR('Budget'[date])=2019)

But this, unfortunately, returns the total sum of this year's budget and doesn't display it by month.

 

Any suggestions for solving this problem are welcomed 🙂  

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
AlexanderAT Regular Visitor
Regular Visitor

Re: Showing 2 years of turnover but only this years budget in line and column chart

I will create a new column for this year's budget. That should solve the problem.

I just thought there would be a smarter way that I have missed. 

 

Thanks for the help though 🙂 

View solution in original post

7 REPLIES 7
Super User
Super User

Re: Showing 2 years of turnover but only this years budget in line and column chart

You could try some of the techniques in my Time Intelligence the Hard Way. Basically using things like ALL to override the calculation context. https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Community Support Team
Community Support Team

Re: Showing 2 years of turnover but only this years budget in line and column chart

@AlexanderAT,

 

Yes, the measure you created will only return an aggregated value which may not meet your requirement.

 ThisYearBudget = CALCULATE((Budget[Budget FBP]), YEAR('Budget'[date])=2019)

I would suggest you to create a slicer based on the year column, when you only want to show the budget value in 2019, just simply select 2019 in the slicer.

 

Regards,

Jimmy Tao

AlexanderAT Regular Visitor
Regular Visitor

Re: Showing 2 years of turnover but only this years budget in line and column chart

This does unfortunately not solve my problem as it will only return the turnover from the selected year. The difficulty is showing the past and present years turnover but only the present years budget.  

Community Support Team
Community Support Team

Re: Showing 2 years of turnover but only this years budget in line and column chart

@AlexanderAT,

 



This does unfortunately not solve my problem as it will only return the turnover from the selected year. The difficulty is showing the past and present years turnover but only the present years budget.  



Could you share some sample data or a sample pbxi file and clarify more details about your requirement?

 

Regards,

Jimmy Tao

AlexanderAT Regular Visitor
Regular Visitor

Re: Showing 2 years of turnover but only this years budget in line and column chart

https://drive.google.com/file/d/1Z-cZxxIKpdJWYyzKJ3lEoaw9hF10eQyN/view?usp=sharing

here is a link to the to a sample pbxi file.

 

In the chart, a want the columns to show the turnover from the last and the present year - This part works correctly.

 

I want the line value only to show the budget for the present year - now it shows the budget for both the last and the present year clustered.  

 

 

 

Community Support Team
Community Support Team

Re: Showing 2 years of turnover but only this years budget in line and column chart

@AlexanderAT,

 

I'm afraid only show budget for present year while show turnover for both previous year and present year can't be achieved. Because the year column, budget and turnover are in same table, when you filter the year, both turnover and budget will be filtered. I would suggest you to create two separate visuals, one visual show the previous and current year turnover value, another visual shows only current value of budget.

 

Regards,

Jimmy Tao

AlexanderAT Regular Visitor
Regular Visitor

Re: Showing 2 years of turnover but only this years budget in line and column chart

I will create a new column for this year's budget. That should solve the problem.

I just thought there would be a smarter way that I have missed. 

 

Thanks for the help though 🙂 

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 275 members 2,813 guests
Please welcome our newest community members: