March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
@THENNA_41 , 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]
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
26 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
40 | |
27 | |
27 | |
21 | |
19 |