Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AlexanderAT
Helper I
Helper I

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

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
v-yuta-msft
Community Support
Community Support

@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

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.  

@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

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.  

 

 

 

@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

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 🙂 

Greg_Deckler
Super User
Super User

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...

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.