How to set up filter function correctly

[anon53427]

Hi! 

I'm fairly new to Power BI. I have two tables. One for groups and one for checks.

Table "groups":

group_id	StartCount
1	16
2	32

Table "checks":

group_id	lost	timestamp
1	0	10.10.2021
1	1	11.10.2021
1	0	12.10.2021
1	1	13.10.2021
1	2	14.10.2021
2	1	10.10.2021
2	0	11.10.2021
2	1	12.10.2021
2	2	13.10.2021
2	0	14.10.2021
...	...	...

I want to add another column to "checks" that gives me the StartCount of table "groups" minus the total "lost" up until that point. Like this:

group_id	lost	timestamp	totalCount	Explaination
1	0	10.10.2021	16	<- Since 0 was lost on this check, the count is StartCount
1	1	11.10.2021	15	<- 15 because: last totalCount minus this lost = 15
1	0	12.10.2021	15	<- 15 because: last totalcount minus 0 is still 15
1	1	13.10.2021	14	<- 14 = last totalCount - 1
1	2	14.10.2021	12
2	1	10.10.2021	31	<- This group has startCount of 32 (but 1 is lost here)
2	0	11.10.2021	31
2	1	12.10.2021	30
2	2	13.10.2021	28
2	0	14.10.2021	28
...	...	...

How do I calculate this formula when adding a new column in data editor?

Thanks! 

[mahoneypat · Accepted solution]

Please try this expression instead.

NewColumn =
VAR startamt =
    RELATED ( Groups[StartCount] )
VAR thistimestamp = Checks[timestamp]
VAR losttodate =
    CALCULATE (
        SUM ( Checks[lost] ),
        ALLEXCEPT ( Checks, Checks[group_id] ),
        Checks[timestamp] <= thistimestamp
    )
RETURN
    startamt - losttodate

Pat

[anon53427]

You're the man! This works great, thanks 🙂 

[mahoneypat]

You could merge the two tables in the query editor or use List functions to do a lookup, but it would be easier with a DAX column (or better still just do it with a measure).  A column expression that should work would be the one below (assuming you have a 1:M relationship between your groups and checks tables on the GroupID column.

TotalCount = RELATED(Groups[StartCount]) - Checks[Lost]

Pat

[anon53427]

Hi! 

Thanks for the reply. This is almost working! The only problem is that it only subtracts the lost count from the same line, so it doesn't take into account the earlier losses. Table now looks like this:

group_id	lost	timestamp	totalCount
1	0	10.10.2021	16
1	1	11.10.2021	15
1	0	12.10.2021	16 (should be 15)
1	1	13.10.2021	15 (should be 14)
1	2	14.10.2021	14 (should be 12)
2	1	10.10.2021	63
2	0	11.10.2021	64 (should be 63)
2	1	12.10.2021	63 (should be 62)
2	2	13.10.2021	62 (should be 60)
2	0	14.10.2021	64 (should be 60)
...	...	...

I guess it should look something like

TotalCount = RELATED(Groups[StartCount]) - SUM(Checks[Lost] where timestamp is earlier than this timestamp) .... or something like that. I don't know this syntax well.

Any tips?