Calculate the latest consecutive row counts

[tonny_bwg]

Dear Gurus:

I have a table containg the past 3 years inventory value for materials. I want to calculate the latest consecutive row counts for each material. please see highlighted cells in attached table.  please help me with it.

file:

thanks

--Tonny

[tonny_bwg]

最大连续 = var FactTable = SELECTCOLUMNS(Sheet1,"Item", Sheet1[辅助列],"日期", Sheet1[DATE_VALUE], "Material",Sheet1[MATERIAL], "FLAG",Sheet1[No_change]) Var FactTablewithindex = ADDCOLUMNS(FactTable, "Index",[Item]) var MasterTable = DISTINCT(SELECTCOLUMNS(FactTable,"index", [item])) var NewTable = SUBSTITUTEWITHINDEX(FactTablewithindex, "index", MasterTable,[index],desc) var ProductTable = ADDCOLUMNS(NewTable,"累积", var s = [Material] var t = [index] return PRODUCTX(FILTER(NewTable, And([Material]=s,[index]<=t)),[FLAG])) Return SUMX(ProductTable,[累积]) this method can found the number of rows with a continuous flag of 1. if don't use column[辅助列] which is combined with [material]&[DATE_VALUE] how to make it work. thanks --Tonny

[amitchandak]

@tonny_bwg , seem very similar to continous streak. You can see one of these solution can help

Continuous Streak -https://community.powerbi.com/t5/Desktop/Need-help-in-DAX/m-p/1277302#M559393
https://community.powerbi.com/t5/Quick-Measures-Gallery/Power-BI-Continuous-Streak-One-Day-Differenc...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1...
https://community.powerbi.com/t5/Quick-Measures-Gallery/Power-BI-Continuous-Streak-One-Day-Differenc...
http://dataap.org/blog/2018/05/30/solved-microsoft-access-the-microsoft-ace-oledb-12-0-provider-is-n...

[tonny_bwg]

最大连续 = var FactTable = SELECTCOLUMNS(Sheet1,"Item", Sheet1[辅助列],"日期", Sheet1[DATE_VALUE], "Material",Sheet1[MATERIAL], "FLAG",Sheet1[No_change]) Var FactTablewithindex = ADDCOLUMNS(FactTable, "Index",[Item]) var MasterTable = DISTINCT(SELECTCOLUMNS(FactTable,"index", [item])) var NewTable = SUBSTITUTEWITHINDEX(FactTablewithindex, "index", MasterTable,[index],desc) var ProductTable = ADDCOLUMNS(NewTable,"累积", var s = [Material] var t = [index] return PRODUCTX(FILTER(NewTable, And([Material]=s,[index]<=t)),[FLAG])) Return SUMX(ProductTable,[累积])

this method can found  the number of rows with a continuous flag of 1.  if don't use column[辅助列] which is combined with [material]&[DATE_VALUE] how to make it work.

thanks 

--Tonny