Self-referencing calculated column to generated ascending number sequence that repeats

[MorePowerBI]

Hello,

I'm trying to recreate the highlighted [Dispense #] column in the JMP table.

The column generates an ascending number sequence for each consecutive row with [ValueName] = "Dispense Weight Chec". The number sequence restarts at 1 if the previous row's value for [ValueName]<>"Dispense Weight Chec".

The JMP code for the column is:
If( :ValueName == "Dispense Weight Chec", If( Lag( :ValueName ) != "Dispense Weight Chec", 1, Lag( :Dispense # ) + 1 )

Not sure if PBI allows calculated columns to self-reference its previous row's value, but that seems to be what I need to generate this column. 

Here's the sample dataset:

Site	ProcessName	EQNumber	ValueName	Value	CreatedDateTime	WC Type	WC Result	Dispense #
SAN	TED Station	EQ-901848-004	Dispense Weight Chec	3.599609375	7/17/2022 12:50	Tip Change		1
SAN	TED Station	EQ-901848-004	DispenseTipXOffset	-0.062082361	7/17/2022 12:53	Tip Change
SAN	TED Station	EQ-901848-004	DispenseTipYOffset	-0.364204079	7/17/2022 12:53	Tip Change
SAN	TED Station	EQ-901848-004	DispenseTipZOffset	1.637206554	7/17/2022 12:53	Tip Change
SAN	TED Station	EQ-901848-004	DispenseTipXOffset	-0.136351258	7/17/2022 12:59	Tip Change
SAN	TED Station	EQ-901848-004	DispenseTipYOffset	-0.399612635	7/17/2022 12:59	Tip Change
SAN	TED Station	EQ-901848-004	DispenseTipZOffset	0.958335042	7/17/2022 12:59	Tip Change
SAN	TED Station	EQ-901848-004	DispenseTipXOffset	-0.129197747	7/17/2022 13:02	Tip Change
SAN	TED Station	EQ-901848-004	DispenseTipYOffset	-0.308497012	7/17/2022 13:02	Tip Change
SAN	TED Station	EQ-901848-004	DispenseTipZOffset	1.200035095	7/17/2022 13:02	Tip Change
SAN	TED Station	EQ-901848-004	Dispense Weight Chec	2.899414063	7/17/2022 13:02	Tip Change		1
SAN	TED Station	EQ-901848-004	Dispense Weight Chec	3.900390625	7/17/2022 13:02	Tip Change		2
SAN	TED Station	EQ-901848-004	Dispense Weight Chec	4.099609375	7/17/2022 13:02	Tip Change		3
SAN	TED Station	EQ-901848-004	Dispense Weight Chec	3.900390625	7/17/2022 13:02	Tip Change		4
SAN	TED Station	EQ-901848-004	Dispense Weight Chec	3.799804688	7/17/2022 13:02	Tip Change		5
SAN	TED Station	EQ-901848-004	Dispense Weight Chec	4.200195313	7/17/2022 13:03	Tip Change		6
SAN	TED Station	EQ-901848-004	Calculated Flow Rate	17.53338432	7/17/2022 13:03	Tip Change
SAN	TED Station	EQ-901848-004	Dispense Weight Chec	4.099609375	7/17/2022 13:14	Inline	Pass	1
SAN	TED Station	EQ-901848-004	Dispense Weight Chec	3.900390625	7/17/2022 13:14	Inline	Pass	2
SAN	TED Station	EQ-901848-004	Dispense Weight Chec	4.200195313	7/17/2022 13:14	Inline	Pass	3
SAN	TED Station	EQ-901848-004	Dispense Weight Chec	4	7/17/2022 13:14	Inline	Pass	4
SAN	TED Station	EQ-901848-004	Dispense Weight Chec	4.200195313	7/17/2022 13:15	Inline	Pass	5
SAN	TED Station	EQ-901848-004	Calculated Flow Rate	17.89508057	7/17/2022 13:15	Inline
SAN	TED Station	EQ-901848-004	Dispense Weight Chec	3.899414063	7/17/2022 13:25	Inline	Pass	1
SAN	TED Station	EQ-901848-004	Dispense Weight Chec	4.100585938	7/17/2022 13:25	Inline	Pass	2
SAN	TED Station	EQ-901848-004	Dispense Weight Chec	4	7/17/2022 13:25	Inline	Pass	3
SAN	TED Station	EQ-901848-004	Dispense Weight Chec	4	7/17/2022 13:26	Inline	Pass	4
SAN	TED Station	EQ-901848-004	Dispense Weight Chec	4.099609375	7/17/2022 13:26	Inline	Pass	5
SAN	TED Station	EQ-901848-004	Calculated Flow Rate	17.94607925	7/17/2022 13:26	Inline

[vapid128]

It is all 13:02, Why there is 1 2 3 4 5?

[vapid128 · Accepted solution]

I create a Line Number to rank this one.

Rank1 =

IF('Table (3)'[ValueName]="Dispense Weight Chec",

    RANKX(

        FILTER('Table (3)','Table (3)'[ValueName]<>"Dispense Weight Chec"),

        'Table (3)'[LineNumber],

        ,ASC),

    BLANK()

)

Rank2 =

IF('Table (3)'[ValueName]="Dispense Weight Chec",

    RANKX(

        FILTER('Table (3)',[Rank1]=EARLIER('Table (3)'[Rank1])),

        [LineNumber],

        ,ASC

    ),

    BLANK()

)

[MorePowerBI]

You wizard!

[MorePowerBI]

Still looking to see if anyone can help out a fellow Power BI addict

[amitchandak]

@MorePowerBI , You can not self-reference, You need to think cumulative. You have to use logic earlier

example

Continuous streak: https://youtu.be/GdMcwvdwr0o

https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1...

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[MorePowerBI]

@amitchandak Yeah I figured I'll need to incorporate EARLIER() somehow. Here's the link to the files: OneDrive