Single Projected Value vs. Multiple Actual Spend

[ProfessorEgg28]

Hello,

I'm trying to create a budget dashboard that has one clustered bar graph that will show:

1) Total spend vs. budgeted amount (year, quarter, month, day via drill down menu)

I have two excel sheets in the sytem. The first is the amount that I have spent on which days and the second is the amount of budget I have for each month. 

My issue with the clustered graph is that the budget amount always shows as the sum of all of the budgeted amounts I've input for the year no matter what option I select for the drill down. What I would like is for the budgetted amount bar to display the budgetted amount for the time period selected via the drill down menu.

In an ideal scenario: 

If I were to drill down to months, I would have for January, spent bar = 30 and budgetted bar = 50, for February, spent bar = 54 and budgetted bar = 50, etc. 

If I were to drill down to quarters, I would have Q1, spent bar = 115 and budgetted bar = 120, etc. 

Lastly, I was wondering if there was a simple way to adjust this to a fiscal year calendar so that the Q1 2024 is changed to Q2 2024 (Fiscal) and Q4 2024 is changed to Q1 2025 (Fiscal) and everything in between. One thought was to create a column that the date spent column and moves it 3 months earlier however, this would make it inaccurate if I were to drill down to months and days.

Thanks in advanced!

[v-kongfanf-msft]

Hi @ProfessorEgg28 ,

As @amitchandak said, a separate date table needs to be created.

Create a one-to-many relationship between the date table and the two tables. Change the date columns in the date table to a hierarchy. Drill down can be done for this date.

Also for fiscal year customization you need to create the relevant columns on the created date table.

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@ProfessorEgg28 , Hope you have created a common date table, joined with date of both tables and using it

Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...