Finding repeated values over consecutive dates

[jlankford]

I hope I can describe this correctly. 

I am building a report, and I need to be able to create a column or measure that essentially says "Alert" or "Okay" if a certain condition is met. 

I have daily inventory data for the last 90 days. I need to be able to identify any time where we have had zero inventory within a 3 day (or longer) stretch. 

Here is an example of what the data looks like:

As you can see, Products "ABC" and "LMN" both have longer than 3-day stretches where there was no inventory on the shelf. I'd like ot create something that creates a column for that product that can tell me that there is an issue and this sku needs to be reordered. 

Thank you. 

[Anonymous]

I need to create a column which count the product type in column A in the fashion as shown in column B. If consecutive months show same product then it should be considered as 1 count. For e.g; the product "a" is counted 2 times because it appeared 2 times but not in consecutive months.

[Anonymous]

I need to create a column which count the product type in column A in the fashion as shown in column B. If consecutive months show same product then it should be considered as 1 count. For e.g; the product "a" is counted 2 times because it appeared 2 times but not in consecutive months.

[Ashish_Mathur]

Hi,

Share some data, describe the question and show the expected result.

[Ashish_Mathur]

Hi,

Could you show your exact result that you are expecting?

[jlankford]

Hi Ashish, 

Thank you for your reply. 

If I could get a column in the matrix, or any sort of indicator that returns which products have an inventory issue (i.e. 5 zeroes in a row) this would achieve what I want. 

One thing to know about the data - it will never be negative and it will never be anything but whole numbers. So if there is a way to add groups of 5 adjacent cells in a row over and over to see if the sum=0, this would achieve it, but I'm unsure of how to do this with DAX or anything else right now. 

[Anonymous]

@jlankford,

Create the following columns in your table.

Indexcol = CALCULATE(COUNT(Table[Date]),ALL(Table),FILTER(Table,Table[Type]=EARLIER(Table[Type])&&Table[Date]<=EARLIER(Table[Date])))

Group = ROUNDUP(Table[Indexcol]/3,0)

Sumbygroup = CALCULATE(SUM(Table[Value]),FILTER(Table,Table[Type]=EARLIER(Table[Type])&&Table[Group]=EARLIER(Table[Group])))

Alert = IF(CALCULATE(MIN(Table[Sumbygroup]),ALLEXCEPT(Table,Table[Type]))=0,1,0)


Regards,
Lydia

[jlankford]

Thank you so much - this works, but too well. 

We have been selling items for 90 days. Unfortunately, the data we download is done in 365 day groupings. As a result, we have 275 days of items that are 0 sales, so every item comes up with an alert.

I thought that a visual level filter might fix it, but I'm guessing that due to the index and group columns, we are able to calculate past the last 90 days. 

Is there a way to limit my view to 90 days since a visual level filter isn't cutting it?

(Also - I need to reiterate - I'm VERY grateful for your help and patience). 

[Ashish_Mathur]

Hi @jlankford,

See if y solution here helps.

[Anonymous]

@jlankford,

I am not very clear about your requirment, do you mean that limit the dates in Matrix to show only 90 days? If so, you can filter dates in visual level filter.

If visual level filter doesn't help, please post complete data of your table and post expected result in screenshot.

Regards,
Lydia

[jlankford]

Hi, 

Thank you again for your reply, and sorry for the delay in getting back to you. 

Below is a screenshot. I used a visual filter to only show the dates 9/28/2017 - 10/6/2017

As you can see, we have ample inventory, and no zeroes. Unfortunately, "ALERT" always reads a number (the reason these numbers are higher than "1" is because I have products belonging to each store that I wish to obscure for now). 

In this data source, it contains the most recent 180 days. For some products, we have had good inventory, so I get "0" for an alert, which is what we want. However, other products, we did not begin selling until 90 days ago, meaning that we have 90 days before it where inventory reads "0". Even with the visual filter showing only 90 days, "alert" still reads a "1" for these products. 

Thank you again for your help and attention. 

[Ashish_Mathur]

Hi @jlankford,

Whom are you replying to?  If it is to my post, then tell me exactly the scenario where it is not working.

[jlankford]

Hi Ashish, 

Thanks for your reply. I'm sorry for not specifying, but I was replying to your post.

In your example above in power pivot, that particular measure successfully marks a "1" everytime there is a "0" in the data, but I need it to mark a "1" on any row that has a stretch of five 0's in succession. 

For instance:

In the example you provided (see image below), you can see that the row "LMN" has five 0's in succession. I need some indicator to let me know that LMN has a grouping of five 0's. 

You can also see in row XYZ there are only two zeros. There is no need to mark these.

I really appreciate everyone's help, and I'm using this as a learning opportunity and not just copy/pasting DAX. Having used standard pivot tables for years, I had no idea there was a whole new world with power pivot and Power BI until just a few months ago. 

Thank you.

[Ashish_Mathur · Accepted solution]

Hi @jlankford,

Download my revised solution from the same link i shared above.

[jlankford]

@Ashish_Mathur

This is exactly what I'm looking for. I'm understanding what these are doing and I'm taking this as a learning opportunity. I see exactly what the difference and fix is between this and the last, and I feel silly it was so simple. 

I appreciate your help immensely. I'm working with my mentors to get access to Power Query and DAX education. 

Thank you.

[Ashish_Mathur]

Hi @jlankford,

You are welcome.

[MarcelBeug]

Maybe you want to learn some Power Query as well?

Actually, Power Query has a function with a special parameter to count consecutive values: Table.Group with 4th parameter GroupKind. If that parameter is set to GroupKind.Local, Table.Group will group consecutive values, so each change in value will become a new group.

So, the challenge is to turn your row values into nested tables and then fire Table.Group on those tables.

This is done with the following query and function GetAlert below. Note that the nested tables are created from all row values except the first 1 (Name). Adjust the Source with your table (I used Power Query in Excel but the rest of the code is fine for Power BI Desktop as well).

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Tables", each Table.FromColumns({List.Skip(Record.FieldValues(_))},{"Values"})),
    #"Invoked Custom Function" = Table.AddColumn(#"Added Custom", "Alert", each GetAlert([Tables])),
    #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"Tables"})
in
    #"Removed Columns"

Function GetAlert:

(MyTable as table) as number =>
let
    #"Grouped Rows" = Table.Group(MyTable, {"Values"}, {{"Count", each Table.RowCount(_), type number}}, GroupKind.Local),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Values] = 0 and [Count] >= 5),
    Alert = if Table.IsEmpty(#"Filtered Rows") then 0 else 1
in
    Alert

[jlankford]

@MarcelBeug

You're absolutely right. I need to learn power query mainly because I'm also facing a ton of memory issues, and even though I'm trying to reduce my fact tables and optimize, I need to work with power query before even bothering with the rest of it. 

As for the functions you posted below, Ashish's solution gets me up and running, and ultimately achieves what I want, but as I'm interested in learning so I can A.) Become self-sufficient and B.) Contribute back to the community, I'll definitely play around with the query as you have demonstrated. 

Thanks for your help and attention. I appreciate it greatly.