Dynamic flag based on date granularity

[Anonymous]

Hello everyone,

I have the table (example) below, it contains dates of orders, email of the client and a “New Customer Flag” (nc_flag with 1 as new customer NC and 0 as returning RC) (this flag has to be imported directly on the table and can’t be a calculated one because I won’t have the full dataset.

The challenge I’m facing is how to use the nc_flag with varying levels of date granularities, for example, client aaa has an order on both the first and second of January, if I’m using a daily granularity the client should appear as a NC (new customer) on the first day and as a RC on the second, however if I’m using a monthly granularity he should only appear as a NC (basically the flag should be the max of the flag depending on the granularity.

I’ve managed to create a measure to calculate the Unique Customers (for NC and RC separately) however I will also need to filter them based on the flag that they have so I’ll need more that this measure.

Can anyone provide some advice on how to achieve this?

Thank you,

The table and measure:

Date	Email	nc_flag
1/1/2021	aaa	1
1/2/2021	aaa	0
1/3/2021	bbb	0
2/4/2021	bbb	0
1/5/2021	ccc	1

Measure 3 = 
VAR summary = SUMMARIZE(Sheet1,Sheet1[Email],"count",max(Sheet1[nc_flag])) 
VAR filtered = FILTER(summary,[count]=1)
RETURN COUNTROWS(filtered)

[v-jingzhang · Accepted solution]

Hi @Anonymous

You can create a measure as a second flag to filter email accounts.

Flag Measure = 
var maxNCFlag = MAX('Table'[nc_flag])
return
if(maxNCFlag=1,1,0)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

To expand some more on the challange I basically need to consider only the first order of each client for that period (the client will always be a NC before being a RC). The problem here is that I need to filter the clients based on their first order after this one has been calculated depending of the time granularity.