Relationship and budget VS actual with yearly result

[manoplaeli]

What I want to build is the Budget vs Actual report by year. When user click on the project, I want to show only avaible year. And then user can look at the report seperate by year. right now I can not create relationship that allow this filter to happen. Whe filter works, the actual value is not correct. Right now what ever year I click, the total actual is still the same and it is not correct value. I know that the issue is comming from how the relationship was build but I just don't know the workaround get around to get it works.

Link below are the Excel and PBIX file for better understanding of the issue.

https://drive.google.com/open?id=1BibPLHxQn4wOxbR1LLnMUDQGVo2i9hDw

 Many thanks

[v-frfei-msft]

Hi @manoplaeli,

Based on my test, we can change your relationship like this and filter the visuals based on the Year in Calendar table.

Here is the result as we excepted.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/vjxccag0j5u8sfs/TEST.pbix?dl=0

Regards,

Frank

[manoplaeli]

Hi Frank,

The filter works but the spending value is not showing correctly. every project has spending in all category

Many thanks,

[v-frfei-msft]

Hi @manoplaeli,

I created the relationship between Calendar and Expenditure based on Year like this, then we can get the result as we excepted.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/d84xleb91oq1reu/Relationship%20and%20budget%20VS%20actual%20with%20yearly%...

Regards,

Frank

[manoplaeli]

Hi Frank

The left hand side is budget and the right is expenditure. It is not showing correctly. 

You can find the excel file below 

https://drive.google.com/open?id=1BibPLHxQn4wOxbR1LLnMUDQGVo2i9hDw

Best,

[v-frfei-msft · Accepted solution]

Hi @manoplaeli,

Seemed that your original calendar table didn't work in my side, So here I recreated another new calendar table and create a calculated column based on that.

New calendar = CALENDARAUTO()

Year = YEAR('New calendar'[Date])

Then I created the relationship between tables like this.

Here is the reslult for your reference.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/d84xleb91oq1reu/Relationship%20and%20budget%20VS%20actual%20with%20yearly%...

Regards,

Frank

[manoplaeli]

Hi Frank,

That works perfectly!

Thank  you very much

Manop