Calculate the number of days the quantity remained constant from a selected date

DAX DAX Calculations
14

.
Well no, because we don’t have to figure anything out - the technique is provided by @BrianJ in post #2
Applying that logic will get you the very same result but it also increases the model size, remember this requires four additional Calculated Columns that will be stored in the fact table of your Model!

RowNo =
VAR vTable = SUMMARIZE( 'Products Movements', Dates[Date], Products[Product], 'Products Movements'[Quantity] )
RETURN

RANKX( vTable, [Date], , ASC, Dense )

.

PrevRowValue =
VAR CurrentDate = 'Products Movements'[Date]
VAR CurrentProduct = 'Products Movements'[Product]
VAR CurrentQuantity = 'Products Movements'[Quantity]
VAR vTable = SUMMARIZE( 'Products Movements', Dates[Date], Products[Product], 'Products Movements'[Quantity], [RowNo] )
VAR PrevRowTable =
    TOPN( 1, FILTER( vTable, [Date]< CurrentDate && [Product] = CurrentProduct ), [Date], DESC, [Product] )

RETURN
    MINX( PrevRowTable, [Quantity] )

.

IsChange = 
VAR vTable = SUMMARIZE( 'Products Movements', Dates[Date], Products[Product], 'Products Movements'[Quantity], [RowNo])
RETURN

SWITCH( TRUE(),
    'Products Movements'[RowNo] = MINX( vTable, [RowNo] ), SWITCH( TRUE(), 'Products Movements'[Quantity] =0, 0 ), 
    'Products Movements'[PrevRowValue] = 0, SWITCH( TRUE(), 'Products Movements'[Quantity] >0, 1, 0 ),
    'Products Movements'[Quantity] = 'Products Movements'[PrevRowValue], 1, 
    0 
)

.

DaysUnchanged = 
VAR CurrentProduct = 'Products Movements'[Product]
VAR CurrentQuantity = 'Products Movements'[Quantity]
VAR CurrentRow = 'Products Movements'[RowNo]
VAR vTable = SUMMARIZE( 'Products Movements', Dates[Date], Products[Product], 'Products Movements'[Quantity], [RowNo], [PrevRowValue], [IsChange] )

VAR LastLossRowNum =
    MAXX( 
        FILTER( vTable, 
            [Product] = CurrentProduct &&
            [Quantity] = CurrentQuantity &&
            [IsChange] = 0 && 
            [RowNo] <= CurrentRow ),
        [RowNo] 
    )

VAR Streak =
    'Products Movements'[RowNo] - LastLossRowNum

 RETURN
     Streak

.

image
image1054×387 14.4 KB

My advise stick with the Power Query solution but hey it’s up to you…
eDNA - Quantity change (PQ & DAX solution).pbix (117.4 KB)

1 Like