Forum Discussion
Dimi_2207
2 years agoHelper I
Serial number based on condition
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 custo...
- 2 years ago
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.
lbendlin
2 years agoSuper User
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.