A way to join tables or keep separate?
Hello, folks. I run a services company. Often my clients ask for a budget summary broken down by fiscal year (including PROJECTED billings). Last year I got smart and added a formula to calculate the fiscal year (e.g. 2025-25 for those starting in January, and 2025-26 for those with a July FY start date) for each billing milestone ([Milestones table]. That enabled me to produce the customer summary table (really a GROUPBY array) in the screenshot.
COMPLICATION
I decided to streamline [Milestones] by moving the invoice detail to another table. Now I have a situation where the PROJECTED billings and their [expected invoice date] are in one table, and the INVOICED billings are in another table with [paid date]. These tables are in the data model (see attached) so i tried to get a pivot table using the linked tables to work, but it is not working as expected. (See data model screenshot.)
QUESTION
Is there a way to output both invoiced and projected billings in one table, grouped by the (customer-specific) FY? In my mind I can create a joined table in the data model, but I am no db expert and I am still struggling with powerquery.
[link] [comments]
Want to read more?
Check out the full article on the original site