Forum Discussion

tonny_bwg's avatar
tonny_bwg
Icon for Helper I rankHelper I
3 years ago

Calculate 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 ...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

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's avatar
    tonny_bwg
    Icon for Helper I rankHelper I
    3 years ago

    最大连续 = 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