Calculate Stock Range in Months

[TimmK]

I have one DB table that includes a column that tracks stock changes (addition, substraction) and another column that shows the remaining stock.

From this I created (matrix) table 1 in Power BI:

• Column R (Year): Calculates the sum of all inventory changes for each article filtered by entry type 3 (= substraction) and within a year

• Column R (Month): Measure that divides R (Year) by 12, so I get the monthly rate

Also, I created table 2 in Power BI:

• Column stock: Calculates the sum of the remaining stock for each article

I now want to combine column stock from table 2 and column R (Month) from table 1, so the first is divided by the latter so I get the duration how long the stock for each article will last in months. How do I do that?

[amitchandak]

@TimmK , you need to have ID as common dimension , then you can have like

sumx(values(ID[ID]), [R Year]+[Stock])

sumx(values(ID[ID]), Sum(Table1[R Year])+sum(Table2[Stock]))

even this should work

[R Year]+[Stock]

refer for ID table

https://www.seerinteractive.com/blog/join-many-many-power-bi/

or

https://www.dropbox.com/s/op9lb78w9utdonz/Distinct%20from%20two%20Tables.pbix?dl=0

[TimmK]

Thank you. Yet, I am not sure if I described my issue correctly.

I only use one single table from my DB (named "Ledger Entry"). From this I created in Power BI on the reports page two tables where in both cases the columns come from "Ledger Entry". One of these tables is filtered by entry type and time, the other one is not filtered. I looked at your link "Refer for ID Table" and it seems rather to describe how to connect two different DB tables via a bridge table. It seems that this does not apply to my issue as I do not need to connect DB tables, but only two tables I created on the report page from the single DB table "Ledger Entry", or am I wrong?