Serial number based on condition

[Dimi_2207]

Hello everyone!

Could, you, please, help me with the following task in DAX.

I am attaching sample in excel.

I have the following columns:

1) "Date" - Call date

2) Customer ID (can be customer phone number)

3) "Repeated calls in 7 days?" - calculated columnt that puts "Y" in row if this is a repeated call (there was a call from the same "Customer ID" within period of 7 days before "Date") , if not - it puts "N".

4) Call number - this is the calculated column i am struggling with..I'd like to assign consecutive number for each call if it happens from the same customer ID within the period of 7 days. If the previous call from id happened earlier - the count starts again from "1".

Date	Customer Id	Repeated calls in 7  Days ?	Call number
24-11-23	1111111	N	1
25-12-23	1111111	N	1
30-12-22	1111111	Y	2
06-01-24	1111111	Y	3
02-02-24	1111111	N	1
04-02-24	1111111	Y	2
09-02-24	1111111	Y	3

Your help will be much appreciated !

[Ashish_Mathur · Accepted solution]

Hi,

These calculated column formulas work

Date of previous N = CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Customer Id]=EARLIER(Data[Customer Id])&&Data[Repeated calls in 7  Days ?]="N"&&Data[Date]<EARLIER(Data[Date])))

Column = if(Data[Repeated calls in 7  Days ?]="N",1,CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Customer Id]=EARLIER(Data[Customer Id])&&Data[Date]>=EARLIER(Data[Date of previous N])&&Data[Date]<=EARLIER(Data[Date]))))

Hope this helps.

[Dimi_2207]

Thank you a lot for you help ! It worked perfectly

[Ashish_Mathur]

You are welcome.

[lbendlin]

There is a typo in row 3 of your sample data - I assume that should have said 30-12-23.  

Please provide sample data that fully covers your issue. Including a second customer.
Please show the expected outcome based on the sample data you provided.

Here is a stub that shows one possible implementation

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLRNTTUNTJW0lEyhAClWB2gsKmuoRGmsLEBVmEDM10DoCEm6MJGukCEKWyCXdgSUzgWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Customer Id" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Customer Id", Int64.Type}},"en-GB"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer Id"}, {{"Rows", each _, type table [Date=nullable date, Customer Id=nullable number]}}),
    #"1111111" = #"Grouped Rows"{[#"Customer Id"=1111111]}[Rows],
    #"Sorted Rows" = Table.Sort(#"1111111",{{"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    AC = Table.AddColumn(#"Added Index", "NewIndex", each List.Accumulate({0..[Index]},0,(state,current)=>if current=0 or #"Added Index"[Date]{current}-#"Added Index"[Date]{current-1}>#duration(7,0,0,0) then 1 else state+1))
in
    AC

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.