Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dynamic flag based on date granularity

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:

 

DateEmailnc_flag
1/1/2021aaa1
1/2/2021aaa0
1/3/2021bbb0
2/4/2021bbb0
1/5/2021ccc1

 

 

 

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

 

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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.

     

2 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    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's avatar
    Anonymous
    Not applicable
    4 years ago

    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.