Implementing an Excel formula as a calculated column/measure

[MR2001]

Hello,

I need to add a custom column or measure that would replicate the follwing Excel 2010 formula. Please note that the formula compares values from current and previous rows:

Excel:

=IF($B3=A$2,-1,0)+IF($C3=A$2,1,0)+IF(ISNUMBER(A2),A2,0)

=IF($B4=A$2,-1,0)+IF($C4=A$2,1,0)+IF(ISNUMBER(A3),A3,0)

=IF($B5=A$2,-1,0)+IF($C5=A$2,1,0)+IF(ISNUMBER(A4),A4,0)

... etc

Thank you,

M.R.

[Sean]

@MR2001

What's in Row 1 - Column Names?

Then what is in Cells B2 and C2?

On What Row goes the result of this =IF($B3=A$2,-1,0)+IF($C3=A$2,1,0)+IF(ISNUMBER(A2),​A2,0) => in D2 or D3

[MR2001]

What's in Row 1 - Column Names?

Yes, column names are in Row 1: [ID], [OLD_STATE], [NEW_STATE], [CALCULATION]. Column D that contains the formula

is [CALCULATION]

Then what is in Cells B2 and C2?

In columns B and C are numbers (integers)

On What Row goes the result of this =IF($B3=A$2,-1,0)+IF($C3=A$2,1,0)+IF(ISNUMBER(A2),​A2,0) => in D2 or D3

In Row D2

Thank you.

[Sean]

What is in cell A2? Can you post a sample data set?

           Column A              B                          C                               D

Row 1 => ID            OLD_STATE        NEW_STATE         CALCULATION

Row 2 => A2

[MR2001]

Sorry Sean, I wasn't clear, in cell A2 is a harcoded string ("H000018") that is also a column header, therefore what I am trying to achieve can be reformulated as follows:

=IF($B3="H000018",-1,0)+IF($C3="H000018",1,0)+IF(ISNUMBER(D2),D2,0)

=IF($B4="H000018",-1,0)+IF($C4="H000018",1,0)+IF(ISNUMBER(D3),D3,0)

=IF($B5="H000018",-1,0)+IF($C5="H000018",1,0)+IF(ISNUMBER(D4),D4,0)

... etc

please find attached screenshot.

Regards,

M.R.

[Sean]

@MR2001 Okay we are getting there but still unclear now about ISNUMBER(D2)

Why do you start at D2 - again start from the Heading but this one is not fixed like $A$2?

If D2 says Calculation it will always give you 0 to start?

@ImkeF does you solution work when A2 says H000018 (for me it only works when A2 says ID)

[ImkeF]

@Sean My solution only works if the column name of the first column is "H0000018" (and not ID). This seems to be the event that needs to be counted.

 ID = List.First(Table.ColumnNames(#"Added Index")), 

is the step that dynamically retrieves the tables column name (Table.ColumnNames) of the 1st column (List.First). I should better have used List.Range instead: You could have used it to retrieve the x-th column name from your tables, so it would have been more flexible.

You are right about the first row: My solution would count that event and not return a 0 like in Excel as you've figured out. (But this could be adjusted if needed).

[ImkeF]

If you want to exactly replicate the Excel-function you'd need recursion or functions that perform recursive operations like List.Generate or List.Accumulate. But they are not easy to use and slow.

But I think that for your desired result, we can use a workaround as it looks like a siimple cumulation here:

let
    Source = YourTable,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    ID = List.First(Table.ColumnNames(#"Added Index")),
    SumIDs = Table.AddColumn(#"Added Index", "SumIDs", each if [Old_State] = ID then -1 else if [New_State] = ID then 1 else 0),
    #"Added Custom" = Table.AddColumn(SumIDs, "CumIDs", each List.Sum(Table.FirstN(SumIDs, [Index])[SumIDs]))
in
    #"Added Custom"

[MR2001]

Thank you for your help Imke, I am trying to implement your code but I get "A cyclic reference was encountered during

evaluation". Sorry, I just started learning this a week ago..

..

[ImkeF · Accepted solution]

No prob - no need to apologize.

You're missing 1 step: "ID" (between #"Added Index" and SumID)

Having trouble reading your screenshots, but have the feeling that you first column-name (where I was expecting to see "H000018")is actually "ID". Then you would have the same problem like Sean and the code wouldn't work.

My solution would only work if you would skip the "ID" and take the column names like in row 2 of your Excel-screenshots.

[MR2001]

Thank you very much Imke for providing this solution, I got it working! 

Regards,

M.R.

[Sean]

@MR2001 its NEVER wrong to go with @ImkeF's solution!

I was going to tell you to try to make it work - that's what I would do to!

[MR2001]

Thank you Sean for spending time on this, I would gladly split the credit for the solution between you and Imke if possible. 

I also realized that a "running total" technique would also provide a similar result:

Measure:

Cumulative Count =
CALCULATE (
SUM ( 'CR Trends'[Test1] ),
FILTER (
ALL ('CR Trends'[history#action_timestamp] ),
'CR Trends'[history#action_timestamp] <= MAX ( 'CR Trends'[history#action_timestamp] )
)
)

where [Test1] is a calculated column that looks like this (I hardcoded the values):

= (if [#"history#old_state"]="Submitted" then -1 else 0) +
(if [#"history#new_state"]="Submitted" then 1 else 0)

Regards,

M. R.