Forum Discussion
Helper ICalculate the latest consecutive row counts
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.
thanks
--Tonny
3 Replies
- amitchandak
Super User
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-Difference/m-p/1290382#M592
https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1290346
https://community.powerbi.com/t5/Quick-Measures-Gallery/Power-BI-Continuous-Streak-One-Day-Difference/td-p/1290382
http://dataap.org/blog/2018/05/30/solved-microsoft-access-the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine/
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
- 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
