Count of months for available inventory based on material type

[Hamdan1234]

Hello,

I have below table and I want to count the number of month for which inventory was available. If inventory was available for 4 months it should show 4 if it is more than 12 months it should not show 12 just keep the limit to for each material inventory to 12 months. I have 4 columns Material, material inventory in hand, material inventory in transit and date. I made the measure to sum up the In hand and transit inventory. Now I want to calculate count of months for total inventory for each material. If the month count would be 3 if inventory was for 3 months september ,october and november. Similarly if the count of month has gone above 12 still it should show 12 count maximum as month should not go above 12. Help would be highly appreciated.

Material	Stock In hand	Stock in transit	Total Stock	Date
A	1084	41	1125	30-Sep-20
B	170	93	262.67604	30-Sep-20
C	991	7	997.58956	30-Sep-20
A	218	84	301.59169	31-Oct-20
B	689	55	743.87554	31-Oct-20
C	1085	22	1107	31-Oct-20
A	71	67	137.5967	30-Nov-20
B	51	66	116.95194	30-Nov-20

[v-yalanwu-msft · Accepted solution]

Hi, @Hamdan1234 ;

You could create a measure as follows:

Count = 
var _count=CALCULATE(DISTINCTCOUNT('Table'[Date].[Month]),FILTER(ALL('Table'),'Table'[Material]=MAX('Table'[Material])&&[Total Stock]>0))
Return
IF(_count<=12,_count,12)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rodrigosan]

But for this forecast, is it not necessary to know the consumption of each item?