topic calculate duration Count between Opening stock and Consumption in DAX Commands and Tips

calculate duration Count between Opening stock and Consumption

Anonymous — Fri, 24 Dec 2021 08:48:23 GMT

i have table in Out_duration . in tables three column Month and opening stock ,Consumation

Month opening stock Consumation

Jan 10 5

Feb 20 2

Mar 10 3

Apr 30 8

May 10 5

Jun 8 6

July 10 7

Aug 9 5

Sep 8 2

Oct 1 1

Nov 1 1

Dec 1 1

Now i want to count Consumation equal to opening stock

for Example Jan Month Openingstock value 10 Consumation 5 less than to openingstock

Refernce Screen shot :

Expected Output:

Month opening stock Consumation Duration

Jan 10 5 3

Feb 20 2 4

Mar 10 3 1

Apr 30 8 4

May 10 5 1

Jun 8 6 1

July 10 7 1

Aug 9 5 4

Sep 8 2 7

Oct 1 1 1

Nov 1 1 1

Dec 1 1 1

i have tire in Excel in the following formula

Duration = MATCH(B2,SUBTOTAL(9,OFFSET(C2,,,ROW(C2:$C$17)-ROW(C2))),1)-1

Looking for Help . thanks in advance

Re: calculate duration Count between Opening stock and Consumption

amitchandak — Fri, 24 Dec 2021 11:07:43 GMT

Anonymous , try to get month number or month rank an try these new column

Cumm New =
sumx(filter(table, [Month No] <= earlier( [Month No]) ), [Consumation])

Duration =
var _cumm = [Cumm New] + [opening stock]
var _Max = Minx(filter(Table, [Cumm New] >=_cumm ),[cumm])
return
maxx(filter(Table, [Cumm New] =_Max ),[Month No]) -[Month No]

Re: calculate duration Count between Opening stock and Consumption

Anonymous — Fri, 24 Dec 2021 11:36:18 GMT

amitchandak let me try now sir