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

[satichun]

I am looking to calculate the number of days the quantity remained constant from a selected date.

After a few unsuccessful attempts (using Earlier, PreviousDay), I tried using the logic in this link and feel I am close (Find Date Of Last Result And Then Difference In A Data Table - Power BI) and switched max date to min date to suit this case and added an extra filter condition on Quantity. However I dont get the right numbers.

Example - If I select 18th Feb, for Product B it should show 2 days. Simialrly
19th Feb - 3 days
20th-4 days
21st- 5 days
22nd-6 days
23rd- 0 days because Quantity on 22nd is 12 which is different from Quantity on 23rd which is 16

Any help please?

Quantity change.pbix (75.3 KB)

[BrianJ]

@satichun,

You may find the following post helpful in solving this. It’s not the exact same case, but very comparable - calculating the number of simultaneous periods in which a number stayed the same sign (as opposed to the exact same value):

Count Consecutive Months that a measure was positive or negative

@nickjordan32, This is a really interesting problem that I’ve been thinking about since you posted it initially, and think I finally cracked it. Had to throw a lot of DAX at it - here are the highlights: Used a series of table functions to create a physical table (pTable) summarizing the relevant fields and measures, where Total Over Under was not blank (but could be zero): pTable = VAR vTable= FILTER( ADDCOLUMNS( SUMMARIZE( Dates, Dates[MonthInCalenda…

• Brian

[satichun]

Thank you Brian. I read through the link question and it is similar to what I am trying to get to. My concern is with calculated columns - if the data is a bit huge then they are detrimental to performance, right? Even the approach I took is based on calc columns, not sure if this could be solved in measures. Will replicate the logic you suggested in the link and come back.

Meanwhile, can you take a minute to help me understand what is wrong with my logic, this should give me the first/min date on which the quantity was consecutively the same as a selected date.

No change Date = 
VAR CurrentDate = 'Product'[Date]
VAR CurrentProduct = 'Product'[Product]
VAR CurrentQuantity = 'Product'[Quantity]

RETURN
CALCULATE( MIN( 'Product'[Date] ),
    FILTER( 'Product', 'Product'[Date] < CurrentDate ),
        FILTER( 'Product', 'Product'[Product] = CurrentProduct ),
            FILTER('Product', 'Product'[Quantity] = CurrentQuantity)
)

[EnterpriseDNA]

Thanks for posting your question @satichun. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

[satichun]

Hi Brian, I implemented the logic in the link you suggested - it worked perfectly when there is a single product, by my data has multiple products and it fails.

I can see this is due to the Row Number and Previous Value calculations in step 2, 3 in your series of table functions. I tried to bring product into the calcs as the below and those didnot work.

PrevRow = VAR PreviousRow =
TOPN(
1,
FILTER(
‘Product’,
’Product’[Date] < EARLIER( ‘Product’[Date] ) && ‘Product’[Product] = ‘Product’[Product]
),
‘Product’[Date],
DESC, ‘Product’[Product]
)

VAR PreviousValue =
MINX(
PreviousRow,
‘Product’[Quantity]
)

RETURN
PreviousValue

Row Number =

RANKX (
ALLSELECTED ( ‘Product’[Date], ‘Product’[Product] ),
‘Product’[Date],
,
ASC,
DENSE
)

Any help please?

Quantity change.pbix (91.4 KB)

[satichun]

Any help please?

Quantity change.pbix (91.4 KB)

[Melissa]

@satichun

I am looking to calculate the number of days the quantity remained constant from a selected date.

Example - If I select 18th Feb, for Product B it should show 2 days.

.

@satichun

I implemented the logic in the link you suggested - it worked perfectly when there is a single product, by my data has multiple products and it fails.

.
Out of sheer curiosity:

• what should happen according to you when multiple Products are selected?
• And how would you like to visualize that?

[satichun]

Hi Melissa

In the final state there is no Product filter - only a filter on a date which is a single select - only 1 specific day will be selected.

A table/bar chart should show me all Products for a selected day and the number of consecutive days the quantity stayed constant.

[Melissa]

So if I understand you correct there will be a filter context on Product level (Product on the Rows or Axis of the visual) but no Slicer

[satichun]

Yes. The viz will look like this. Hid the Y axis header of the bar chart on the right.

[Melissa]

Ok so here is a solution for you.

The heavy lifting is done in Power Query, where I added a column called PrevDate to the Stock movements. Know that this was based on the fact that you had a single Quantity for each Product and Date (if you have multiple transactions, you can use Power Query’s Group by to create this).
You can go over the Steps in the Advanced Editor.

Did some modelling while I was at it…

.
And what was left, two simple measures:

Quantity by Product = 
SUM( 'Products On Hand'[Quantity] )

# of Days with no Qty change by Product = 
DATEDIFF( SELECTEDVALUE( 'Products On Hand'[PrevDate] ), SELECTEDVALUE( 'Products On Hand'[Date] ), DAY ) -1

.
With this result:

Here is my sample file. eDNA - Quantity change (PQ solution).pbix (114.1 KB)
I hope this is helpful.

[satichun]

Thank you very much Melissa. This works great.

I glanced at the advanced editor and the PQ steps - was a little over my head. Need to go through it step by step to grasp whats going on with the functions. Will do that in the next few hours.

For my understanding and learning - is it very complex to create that PrevDate in DAX? I thought I was close to getting this with Brian’s approach or even with my first measure which is this

No change Date =
VAR CurrentDate = ‘Product’[Date]
VAR CurrentProduct = ‘Product’[Product]
VAR CurrentQuantity = ‘Product’[Quantity]

RETURN
CALCULATE( MIN( ‘Product’[Date] ),
FILTER( ‘Product’, ‘Product’[Date] < CurrentDate ),
FILTER( ‘Product’, ‘Product’[Product] = CurrentProduct ),
FILTER(‘Product’, ‘Product’[Quantity] = CurrentQuantity)
)

[satichun]

Also thank you for the data modelling - I see the Product dim table, 1 to many and filters flowing from the 1 to many and the hidden fields in the fact table.

[Melissa]

@satichun

is it very complex to create that PrevDate in DAX?

.
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

.

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)

[EnterpriseDNA]

Hi @satichun, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!

[satichun]

As always - thank you @Melissa. I am sticking with the Power Query solution - was only asking for the dax piece to see where I pivoted off in the way to the solution. I now know that its mostly Summarize, then row no.