Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tonny_bwg
Helper I
Helper I

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 table.  please help me with it.

file:

thanks

--Tonny

3 REPLIES 3
tonny_bwg
Helper I
Helper I

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors