Count unique records based on total value for transactions in given month

[JustinM]

Hi

I have the following fields where I want to select a period (say the latest one) and where the sum of all costs of a ID in that month is larger than 0, to then distinct count it

The  Period is in yyyymm format and in order to use the formula for various months I’ve brought in Date#

Period	Transact	Cost	ID
202101	1	100	1
202101	2	(100)	1
202101	1	150	2
202101	2	50	2
202101	1	200	3
202101	1	300	4
202102	1	150	2
202102	2	(200)	2
202102	1	200	3
202102	1	300	4

The expected answer for example for 202101 would  be a count of 3 as the sum of ID 1 =0  

and 202102 would be 2 as the sum of ID 2 is less than 0.

I roughly came up with this but it returns zero and I am still new to this so have no idea where I’m going wrong.

=Count(DISTINCT Aggr(If(and({<Period={"$(=Max(Date(Date#(Period,'YYYYMM'),'YYYYMM')))"},(Cost>'0')>})) , ID))

Any help much appreciated

[Saravanan_Desingh · Accepted solution]

Try this,

Dim: Period

Exp: Count(Aggr(If(Sum(Cost)>0,ID),Period,ID))

[JustinM]

Thanks - That works great

[JustinM]

How would I change the above formula to only show current month if multiple months are selected and I for example only want to show a KPI chart without Period as dimension? Or say the previous month?