new column that tags every first appearence of a number within a month

[AFra]

Hi all,

I have a measure that allows me to calculate the number of calls within a year following three conditions : 

- [CallType] = C

- [Durée]<=30 AND <120 (seconds)

- Distinct [Clip] (phone number) per month : if I have 2 identic [Clip] within a month it counts only once, but if the same [Clip] appears in two different months we count it once in each month) + all [Clip]=BLANK()

CALLS < 2Min = 

SUMX(
            VALUES(CallHistory[Année-mois]), 
            CALCULATE(
                DISTINCTCOUNTNOBLANK(CallHistory[Clip])
                + CALCULATE(COUNTROWS(CallHistory), CallHistory[Clip]=BLANK()),  
                CallHistory[CallType]="C",
                FiltreAppels[Durée]>=30, 
                FiltreAppels[Durée]<120
        )   
    )

I need to create a column such as IF(same rules as the measure), "1", "0"). 

In other words, I need a column that : 

IF (AND(CallHistory[CallType]="C",

              AND(FiltreAppels[Durée]>=30,

                      AND(FiltreAppels[Durée]<120, 

                              OR(CallHistory[Clip]=BLANK(), [Clip.... ??]

[Clip.... ??] = first appearence of this Clip within the month

Any idea on how do I write that? 

Thanks in advance, 

Ana 

[Ashish_Mathur · Accepted solution]

Hi,

Share data in a format that can be pasted in an MS Excel file.

[AFra]

Hello, 

I need to add a new condition to this calculation : if the number ([Clip]) is already flagged as "Reactif" within the month, I should not count it. In other words, to my column "CALLS < 2 min" 

CALLS < 2Min = if(CALCULATE(COUNTROWS(Data), FILTER(
                  Data,Data[Clip]=EARLIER(Data[Clip])&&
                  Data[CallType]="C"&&
                  Data[Durée]<=120&&
                  Data[StartOfCall]<=EARLIER(Data[StartOfCall])&& 
                  Data[Month of call]=EARLIER(Data[Month of call])))=1,1,BLANK())

I need to add the condition "if it's flagged Reactif whitin the month then BLANK()

Here's a sample of the data  

@Ashish_Mathur any inspiration on how to do this? 

[Ashish_Mathur]

Hi,

That download link opens up an error page.

[AFra]

Hi, could you please try this link : https://we.tl/t-XeMJWDtxqy?utm_campaign=TRN_TDL_05&utm_source=sendgrid&utm_medium=email&trk=TRN_TDL_...

Thank you in advance!

[Ashish_Mathur]

Hi,

Try this calculated column formula

CALLS < 2Min = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Clip]=EARLIER(Data[Clip])&&Data[Yearmonth]=EARLIER(Data[Yearmonth])))>0,BLANK(),if(CALCULATE(COUNTROWS(Data), FILTER(
                  Data,Data[Clip]=EARLIER(Data[Clip])&&
                  Data[CallType]="C"&&
                  Data[Durée]<=120&&
                  Data[StartOfCall]<=EARLIER(Data[StartOfCall])&& 
                  Data[Month of call]=EARLIER(Data[Month of call])))=1,1,BLANK()))

Hope this helps.

[AFra]

Thank you so much! 

It works nicely, I only made one adaptation : instead of "Month of call" I used "YearMonth of call".  

[Ashish_Mathur]

You are welcome.

[AFra]

sure. thanks in avdvance!!! 

heres the wetransfer link

[Ashish_Mathur · Accepted solution]

Hi,

Write these calculated column formulas

Month of call = MONTH(Data[StartOfCall])

Column = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Clip]=EARLIER(Data[Clip])&&Data[CallType]="C"&&Data[Durée]<=120&&Data[StartOfCall]<=EARLIER(Data[StartOfCall])&&Data[Month of call]=EARLIER(Data[Month of call])))=1,1,BLANK())

Hope this helps.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.  Do you want a measure solution?

[AFra]

Hi Ashish, 

thank you for your suggestion : ) 

heres a table of an example. The yellow column is the expected result and the 'reason' column explains why it counts as 1 or BLANK. 

[Ashish_Mathur · Accepted solution]

Hi,

Share data in a format that can be pasted in an MS Excel file.