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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Jamie_Macca
Frequent Visitor

Relate tables based on date in column header

I have 2 tables one showing Budgets for different spend types for each month and an 8 month total forcast. e.g.

Jamie_Macca_0-1698841963408.png

The other table shows actual spend that has a Spend type (same as the budget spend types), amount spent, name of spend and a Date feild showing when a spend was made I want to be able to show Actual spend against the budget per monthly target.

 

The issue I have is that I cannot relate the Date of the actual spend to the Month Date in the header of the budget table so I can filter on for example Oct-23 target and bring back all acutal spends from the Acutal spend table to plot against the budget.

 

 Do I need to go into the data and add Date feild for each spend type in the budget? similar to the below to allow me to filter on Month? or is ther an easier way?

 

Jamie_Macca_1-1698842451755.png

 

1 ACCEPTED SOLUTION
Adescrit
Impactful Individual
Impactful Individual

Hi @Jamie_Macca 

 

You would need to transform your table so that the dates are in rows, similar to the example you have provided at the end of your post. Fortunately you don't need to do that in the raw data, you can do that in Power Query.

 

  1. Open Power Query by clicking on Transform Data.
  2. Select the Date columns from the budget table, e.g. Oct-23, Nov-23. You can use ctrl or shift to select multiple columns.
  3. Then in the Menu ribbon go to Transform - Unpivot Columns.

 

Adescrit_0-1698843315502.png

 

Best practice would then be to have a date dimension table that filters both the budget table and the spend table, rather than relating the budget table directly to the actual spend table.


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

View solution in original post

2 REPLIES 2
Jamie_Macca
Frequent Visitor

@Adescrit Thank you so much, I've never used the Unpivot Function.

 

I thought there must be an easy/repeatable way of doing it. 

 

Unsure what the date dimension table is but can look that up.

 

Thanks Again!

Adescrit
Impactful Individual
Impactful Individual

Hi @Jamie_Macca 

 

You would need to transform your table so that the dates are in rows, similar to the example you have provided at the end of your post. Fortunately you don't need to do that in the raw data, you can do that in Power Query.

 

  1. Open Power Query by clicking on Transform Data.
  2. Select the Date columns from the budget table, e.g. Oct-23, Nov-23. You can use ctrl or shift to select multiple columns.
  3. Then in the Menu ribbon go to Transform - Unpivot Columns.

 

Adescrit_0-1698843315502.png

 

Best practice would then be to have a date dimension table that filters both the budget table and the spend table, rather than relating the budget table directly to the actual spend table.


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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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