topic Re: Cumulative total based on sequence number in DAX Commands and Tips

Cumulative total based on sequence number

Anonymous — Fri, 17 Apr 2020 13:30:06 GMT

I need some help with a new measure or calculcated colum/table. I'm not sure how to go about doing what I need to do. This is what I have at this point for my data. Column 1 is the sequence number, 2 is the part number, 3 is the date, 4 is the quantity needed at the start of the day, 5 is the quantity that has been delivered to the department since the start of the day and then this is where I have issues. I want the final column to show the total quantity left to be delivered for that specific sequence number. So for sequence 1, the total still needed is 36 but for sequence 6 (same part number) I want it to be a cumulative of sequence 1 and 6 so the total would be 92 and not 0. I'm not sure how to go about doing that. Another example would be sequence numbers 3 and 5, where I want 3 to show 0 needed and 5 to show we still need 36. Any help would be appreciated!!

Sequence	Item	Date	QuantityToBuild	Qty Delivered	Qty Needed	Total Needed for Day
1	385CS1	5/18/2020 0:00	336	300	36	92
2	386CS1	5/18/2020 0:00	450		450	450
3	387CS1	5/18/2020 0:00	112	300	0	36
4	2249CS1	5/18/2020 0:00	224		224	224
5	387CS1	5/18/2020 0:00	224	300	0	36
6	385CS1	5/18/2020 0:00	56	300	0	92
7	2248CS1	5/18/2020 0:00	224		224	224
8	4909CS1	5/18/2020 0:00	560		560	560
9	4910CS1	5/18/2020 0:00	112		112	112
10	9441CS1	5/18/2020 0:00	56		56	56
11	9442CS1	5/18/2020 0:00	56		56	56
12	6971CS1	5/18/2020 0:00	90		90	90
13	6960CS1	5/18/2020 0:00	90		90	90
14	4797CS1	5/18/2020 0:00	90		90	90
15	7516CS1	5/18/2020 0:00	300	100	200	200
16	6950CS1	5/18/2020 0:00	30		30	30
17	6955CS1	5/18/2020 0:00	30		30	30
18	2647CS1	5/18/2020 0:00	30	100	0	0

Re: Cumulative total based on sequence number

Anonymous — Fri, 17 Apr 2020 13:31:16 GMT

FYI - the QTY delivered is from a different table and is being pulled in with a relationship tied to the Item.

Re: Cumulative total based on sequence number

Greg_Deckler — Fri, 17 Apr 2020 13:53:25 GMT

Maybe these two columns:

CumulativeQuantityToBuild = SUMX(FILTER('Table (4)',[Sequence] <= EARLIER([Sequence]) && [Item] = EARLIER([Item])),[QuantityToBuild]) Qty Needed For Sequence = VAR __Needed = [CumulativeQuantityToBuild] - [Qty Delivered] RETURN IF(__Needed < 0,0,__Needed)

Re: Cumulative total based on sequence number

Anonymous — Fri, 17 Apr 2020 15:51:30 GMT

I cant seem to get this to work - potentially because the main table I'm working off of is a calculated table? Not sure if that matters or not.....Maybe I need to add more to my already calculated table? Here is calculation for the current table I'm using

Table 2 = VAR temp = ADDCOLUMNS ( 'Table', "MinS", VAR s = [Sequence] VAR C = [Item] RETURN MINX ( FILTER ( 'Table', VAR c1 = [Item] VAR s1 = [Sequence] RETURN CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( ALL ( 'Table' ), 'Table'[Item] = c1 && 'Table'[Sequence] <= s && 'Table'[Sequence] >= s1 ) ) = s - s1 + 1 ), [Sequence] ) ) RETURN SELECTCOLUMNS ( SUMMARIZE ( temp, [MinS], "Sequence", RANKX ( temp, [MinS],, ASC, DENSE ), "Item", MAX ( 'Table'[Item] ), "QuantityToBuild", SUM ( 'Table'[QuantityToBuild] ), "Date", MAX ( 'Table'[Date] ) ), "Sequence", [Sequence], "Item", [Item], "QuantityToBuild", [QuantityToBuild], "Date", [Date] )

Do I just need to add to this?