New Column with If Statement Referencing Previous Value +1

[moored]

Hi There,

I am stuck here and hoping someone can point me in the right direction as I have searched everywhere and am coming up with nothing. I have a table with login tracking data and I need to have a calculated column that is built off the previous value. I can do this very easily in Excel with the following data and formula:

• "LOGIN" value in column B results in +1
• "SYSLOGOUT" value in column B results in no change
• Any other value in column B results in -1

=IF(B6="LOGIN",E5+1,IF(B6="SYSLOGOUT",E5,E5-1))

A	B	C	D	E
ATTEMPTDATE	ATTEMPTRESULT	USERID	LOGINTRACKINGID	CONCURRENT LOGIN COUNT
1/1/2019 1:02	SYSLOGOUT	DKELLOGG	6209259	0
1/1/2019 3:01	SYSLOGOUT	MOSTROM	6209546	0
1/1/2019 8:36	LOGIN	GVILLALOBOS	6209663	1
1/1/2019 8:44	TIMEOUT	GVILLALOBOS	6209668	0
1/1/2019 9:26	LOGIN	ANEUZIL	6209675	1

Is this even possible in Power BI?

Thanks!

-Douglas

[ryan_mayu · Accepted solution]

@moored 

you can create two columns

Column = if('Table'[ATTEMPTRESULT]="LOGIN",1,if('Table'[ATTEMPTRESULT]<>"SYSLOGOUT",-1,0))

Column 2 = SUMX(FILTER('Table','Table'[ATTEMPTDATE]<=EARLIER('Table'[ATTEMPTDATE])),'Table'[Column])

[amitchandak]

@moored , Try a new column like

Sumx(filter(Table,[USERID] =earlier([USERID]) && [ATTEMPTDATE] <= earlier([ATTEMPTDATE])), if([ATTEMPTRESULT] ="LOGIN", 1, If([ATTEMPTRESULT] = "SYSLOGOUT",0,-1)))