calculated column

[Haja007]

Hello everyone, I'm a beginner in power bi and I don't know if my problem is because of my model or something else.

In short, I have a table containing lists of products with several columns including inventory date, end of month stock (stock), quantity consumed during the month (qtCons). I would like to add another column to have the month of stock available (stock/qtCons) on the inventory date, in other words if the inventory date is the end of January, I would like to have the month of stock available (stock January/ consumption average three of the last month). my problem is that once I am on line at the end of January how to recover the average qtCons of November, December and January ?

Products	store	Inventory date	end of month Stock	qtCons during the monthe	month of stock available
Product A	S1	2023-01-31	12	23	12/average quantity consumed during the last three months including the month of inventory(november,december,january)
Product B	S1	2023-01-31	23	43	23/average quantity consumed during the last three months including the month of inventory(november,december,january)
Product C	S1	2023-01-31	32	2	...
Product A	S2	2023-01-31	2	35	...
Product B	S2	2023-01-31	5	67	...
Product C	S2	2023-01-31	76	0	...
...	....	..	..	..	...

Thank you so much !

[talespin · Accepted solution]

Hi @Haja007 

Assuming that date will always be monthend date. If not please modify the date condition accordingly.

(EOMONTH( _InvDate, -3) + 1) && QtyTbl[Inventory date] <= _InvDate

Above formula first subtracts 3 months from current date and return end of month and +1 to give beginning of next month.

New Col =

VAR _Product = QtyTbl[Products]

VAR _Store = QtyTbl[store]

VAR _InvDate = QtyTbl[Inventory date]

VAR avgQtyLast3Mths =

                    CALCULATE(

                                AVERAGE(QtyTbl[qtCons]),

                                REMOVEFILTERS(QtyTbl),

                                QtyTbl[Products] = _Product,

                                QtyTbl[store] = _Store,

                                QtyTbl[Inventory date] >= (EOMONTH( _InvDate, -3) + 1) && QtyTbl[Inventory date] <= _InvDate

                    )

RETURN DIVIDE( QtyTbl[EOMStock ], avgQtyLast3Mths)