Add together 2 tables by milestone date & cash value

[JanikSchaefer]

Hey guys,

I have the following two tables in Power Query:

Project	Milestone 1	Milestone 2	Milestone 3
Project A	15.04.2021	05.07.2021	05.08.2021
Project B	…	…	…
Project C	…	…	…

 and

Project	$ amount	Date
Project A	$    7,855.49	01.04.2020
Project A	$    7,726.43	01.04.2020
Project A	$    9,033.62	01.05.2020
Project A	$    9,707.64	01.06.2020
Project A	$    7,307.94	01.06.2020
Project B	$    8,120.81	01.07.2020
Project B	$    5,940.69	01.08.2020
Project B	…	…
Project B	…	…
...	....	...

And would like to add together all cash values that happened before the date of a certain milestone AND after the last milestone.

Table C represents this:

Project	Milestone 1	Milestone 2	Milestone 3
Project A	$  15,581.92	$     34,170.01	$    5,940.69
Project B	…	…	…
Project C	…	…	…

 (wouldnt mind if it were unpivoted)

Is there an easy way to program this in Power Query M / to do this in general?

There are 10 milestones and 130 projects.

Thanks for your help!

Cheers,

Janik

[JanikSchaefer]

This is exactly what I looked for, thank you! 🙂

[v-rzhou-msft · Accepted solution]

Hi @JanikSchaefer 

I think you  want to calculate the total amount by project and date (month, day) should in the range of Milestone(1,2,3).

I build a sample to have a test.

TableA:

TableB:

Transform TableA. We need unpivot all Milestone columns and add a new MonthDay column.

MonthDay = Date.Month([Value])*100+Date.Day([Value])

Transform TableB. Add a new MonthDay column.

Merge two Tables as a new table. Then Expand TableA.MonthDay.We need to add a Conditional Column.

Then Remove the rows which has null values. Group by Project and amount to get the min value in custom column(Mincustom).

Group by Mincustom and Project to get Total amount.

Merge this table with tableA. Right outer by Project column and Monday column.

Expand TableA.Project and Attribute.

Remove origin Project and Mincustom column.

Finally select attribute and sumamount column and pivot column to get the result.

You can download the pbix file from this link: Add together 2 tables by milestone date & cash value

Best Regards,

Rico Zhou

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

[JanikSchaefer]

Hello @v-rzhou-msft ,

thanks for your help so far.

There seem to be 2 problems with this:

1)
In step "Then Remove the rows which has null values. Group by Project and amount to get the min value in custom column(Mincustom)."

Whenever I group here, and there are two equal amounts in one project, one of them gets lost, thus not summing up all the values(amount) that there are. For example if there is an invoice of 2000 and another one of 2000, only 2000 is summed up, not 4000.

2) In step "Expand TableA.Project and Attribute."

---

---

Here, when there are different milestones with the same date, they all get the value, even though only the first (order I can choose by ordering the milestones)  should get it.

 

Do you/anyone have any solution to this problem?

I would be really glad.

Thanks for your help,

Janik