How to connect date values with month values? and make a summary pivot table?

Hello

I want to make a summary table from four separate tables named labor, spray, fertilizer, and land rent. The data are presented daily for labor, spray, and fertilizer tables, but the data for land rent is presented monthly.

To make a summary table, I created a relationship by using power pivot among labor, spray, fertilizer tables, and three filter tables to get the desired result. I am using filter tables because there is duplication in connecting columns. My actual problem is how can I connect the land rent table to make a summary pivot table? Because land rent data is presented monthly. Also, there is another condition that there are two types named Orchard and vegetables. Both of these types have subgroups. Orchard rent is presented combinedly (sub-groups are not important) but the Vegetable rent is presented on a sub-group basis (sub-groups are important).

It will save a lot of my time if an expert suggests me a suitable way to achieve my desired result. Please check the attached Excel file for a better understanding of the problem. Thanks in advance.

Perhaps this will help:
1. Convert the data range on the Land Rent into an Excel table.
2. Use the following Power Query Advanced Editor code to convert the matrix Land rent table into a records-based table:

Please Login or Register  to view this content.

3. Add the new table to the data model
4. Replace the Filter_date table with a calendar table (Excel will make one)
5. Pull the Rent field into the Values area
6. Get the Total cost using: =sum(tbl_LandRent[Rent])+sum(Labor_cost[Labor cost (Tk)])+sum(Spray_cost[Spray cost (Tk)])+Sum(Fertilizer_cost[Fertilizer cost (Tk)])
Let us know if you have any questions.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

That's the way it is - you will be able to after you have given rep to some other members.

RE: Post #3 You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.