Forum Discussion
Summarize inventory table with countinous dates with inventory table with amount per month
Hi,
I have two tables for two different inventories.
In one table I have daily changes in inventory, like:
| 19.03.2023 | + 5 000 |
| 20.03.2023 | - 2 000 |
| 21.03.2023 | + 12 500 |
| 22.03.2023 | - 8 000 |
This table continues over several years, and the sum of all transacations is e.g. 8 000 000,- as of 22.03.2023
In the second table the inventory amount is posted at the end of every month, like:
| 31.01.2023 | + 1 800 000 |
| 28.02.2023 | + 2 000 000 |
| 31.03.2023 | + 2 600 000 |
(This is an accrual table, so the same amount is posted in negative with the first of every month, but I have created the new table with the EOMONTH-function)
I want to create a measure where I can summarize per date the amount from inventory 1 and the amount from the previous month from inventory 2.
So for alle dates from 28.02.2023 until 30.03.2023 I want SUM(Inventory 1) + 2 000 000.
For the 22.03.2023 the sum would be 8 000 000 + 2 000 000 = 10 000 000.
I found a solution for the problem.
In the accrual table, the reversal from last month is posted on the first, and the accruals for this month is posted on the last day. In Power Query I summarized all transactions per month, and set the date as the last of the month for the summarized amuont. This gave me the changes each month, with date last of the month.
I first created a Calendar table called Dates, and a column Date, with all consecutive dates.
I then created the measure
Accumulated amount = Calculate(SUM(Amount), All(Dates[Date]), Dates[Date] <= MAX(Dates[Date]))This formula I could you for both inventories, and I could also make one that summarized the two measures to find total inventory amount.
2 Replies
- lbendlin
Super User
ok, sounds straightforward. What have you tried and where are you stuck?
I found a solution for the problem.
In the accrual table, the reversal from last month is posted on the first, and the accruals for this month is posted on the last day. In Power Query I summarized all transactions per month, and set the date as the last of the month for the summarized amuont. This gave me the changes each month, with date last of the month.
I first created a Calendar table called Dates, and a column Date, with all consecutive dates.
I then created the measure
Accumulated amount = Calculate(SUM(Amount), All(Dates[Date]), Dates[Date] <= MAX(Dates[Date]))This formula I could you for both inventories, and I could also make one that summarized the two measures to find total inventory amount.
