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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bbhs
New Member

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.

1 ACCEPTED SOLUTION
bbhs
New Member

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. 

 

View solution in original post

2 REPLIES 2
bbhs
New Member

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. 

 

lbendlin
Super User
Super User

ok, sounds straightforward.  What have you tried and where are you stuck?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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