calculated column

Question

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 Answer

Hi&nbsp;Haja007&nbsp;&nbsp;Assuming that date will always be monthend date. If not please modify the date condition accordingly.(EOMONTH( _InvDate, -3) + 1) &amp;&amp; QtyTbl[Inventory date] &lt;= _InvDateAbove formula first subtracts 3 months from current date and return end of month and +1 to give beginning of next month.&nbsp;New Col =VAR _Product = QtyTbl[Products]VAR _Store = QtyTbl[store]VAR _InvDate = QtyTbl[Inventory date]VAR avgQtyLast3Mths = &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE( &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AVERAGE(QtyTbl[qtCons]), &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; REMOVEFILTERS(QtyTbl), &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; QtyTbl[Products] = _Product,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; QtyTbl[store] = _Store,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; QtyTbl[Inventory date] &gt;= (EOMONTH( _InvDate, -3) + 1) &amp;&amp; QtyTbl[Inventory date] &lt;= _InvDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )RETURN DIVIDE( QtyTbl[EOMStock ], avgQtyLast3Mths)&nbsp;&nbsp;&nbsp;

Forum Discussion

calculated column

1 Reply

Related content

TMDL - calculated column

Direct Lake Model - Calculated Column/Tables

IF calculated column

Power Query or Calculated columns

Dynamic Variable in Calculated Columns