Forum Discussion
Count each occurence based on a timeframe
Community SupportHi LexN ,
I am not sure if I understood your question correctly. Please check the following calculated columns to see if they meet expectations.
Is_0_Case =
VAR TimeLimit = 5
VAR UserID = DataSource[UserID]
VAR SubjectCode = DataSource[SubjectCode]
VAR CaseDate = DataSource[CreationDate]
VAR nextdate =
CALCULATE (
MIN ( DataSource[CreationDate] ),
FILTER (
DataSource,
UserID = DataSource[UserID]
&& SubjectCode = DataSource[SubjectCode]
&& CaseDate < DataSource[CreationDate]
&& DataSource[Status] = "Valid"
)
)
VAR diff =
DATEDIFF ( [CreationDate], nextdate, HOUR ) / 24
RETURN
IF ( diff >= 5 || diff = BLANK (), "0" )CaseCount_output =
VAR last_0 =
CALCULATE (
MAX ( [CreationDate] ),
FILTER (
DataSource,
DataSource[SubjectCode] = EARLIER ( DataSource[SubjectCode] )
&& DataSource[CreationDate] <= EARLIER ( DataSource[CreationDate] )
&& [UserID] = EARLIER ( DataSource[UserID] )
&& DataSource[Is_0_Case] = "0"
)
)
VAR _count =
COUNTROWS (
FILTER (
DataSource,
DataSource[SubjectCode] = EARLIER ( DataSource[SubjectCode] )
&& DataSource[CreationDate] <= EARLIER ( DataSource[CreationDate] )
&& [CreationDate] > last_0
&& [UserID] = EARLIER ( DataSource[UserID] )
)
)
RETURN
IF ( DataSource[Is_0_Case] = "0", 0, _count )
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thank you for looking into this. Your results are prety close to what is desired. But still, the output has the same issue that I previously encountered. The count doesn't reset when that time limit of 5 days is reached.
I don't know if I am explaining this right, but, for example, the first 3 rows in the table should be included in the first group because their creation dates are within the 5 days time limit. Next, the 4th row should be the first case in a new group, because the time diff between it's creation date and the creation date of the first case in the first group is over 5 days, and so on.
The count on the Desired CaseCount column is what I am trying to achieve.
The main obstacle is that I cannot find a way to reset the case counter when the difference between the current creation date and the creation date of the first case in the previous group is over 5 days.
Thank you again for your effort, maybe now I explained the problem better and you have the right information to build upon.
- v-kkf-msft
Hi LexN ,
Please open a blank query and paste the code.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZRLagMxEESvYmZtcLe+M7pBLpCN8SLgjSFkmfNHFrJsqStWj6A3Mzz6U0WdzwvntxwXc3InQ8YcmJNb8wfm+ufz6/t2vX8odTm+IL4iIZkNIaYURGKyELGlOoSpMiZRRIyTk7GtjE0E+3g5Gj8u4BL5V+bj57dSh1KY2v65WwQb1SvQNnR6MGvZCjL52GQRc1+TCmPy2ylpQ/SSCmQuqc1v52AN0Q8mkB2DqbxGA+MVXhu24TBxTZDrcJy4JoJ94sQ1T6e5/NTiUI+wRh3CSVDbGFMN9SZvQt9FICBvQu8BgaC84d4DgkF543oPCAblje/zpjHv88b3zhGdUN6E3jmCQXkTeucIBuSNXtKG6CUVyFxSkQTzwRqiH0wgOwZTeW3Mm6Dw2pg3ceIalDfrxDUob9aJa0DeaMQZ80ajTmYufw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SubjectCode = _t, CreationDate = _t, UserID = _t, Status = _t, CaseCount = _t, #"Desired CaseCount" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"SubjectCode", Int64.Type}, {"CreationDate", type datetime}, {"UserID", Int64.Type}, {"Status", type text}, {"CaseCount", Int64.Type}, {"Desired CaseCount", Int64.Type}}), #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([CreationDate]), type date), #"Sorted Rows" = Table.Sort(#"Inserted Date",{{"SubjectCode", Order.Ascending}, {"UserID", Order.Ascending}, {"Date", Order.Ascending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"SubjectCode", "UserID"}, {{"allrowtab", each _, type table [SubjectCode=nullable number, CreationDate=nullable datetime, UserID=nullable number, Status=nullable text, CaseCount=nullable number, Desired CaseCount=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "StartDate", each let tab = Table.SelectRows([allrowtab], each [Status] = "Valid"), cdate = Table.SelectColumns(tab, "Date") in List.Generate(()=>[x=0,y=Record.Field(cdate{0}, "Date"),w=1],each [w] > 0,each [z=[y], x=Table.SelectRows(tab,each [Date]>Date.AddDays(z,5))[Date],y=x{0},w=List.Count(x) ], each [y])), #"Expanded allrowtab" = Table.ExpandTableColumn(#"Added Custom", "allrowtab", {"CreationDate", "Status", "CaseCount", "Desired CaseCount","Date"}, {"CreationDate", "Status", "CaseCount", "Desired CaseCount","Date"}), #"Added Custom1" = Table.AddColumn(#"Expanded allrowtab", "IsStartDate", each if List.Contains([StartDate], [Date]) then 0 else null) in #"Added Custom1"Then create the calculate column.
CaseCount_output = VAR last_0 = CALCULATE ( MAX ( [CreationDate] ), FILTER ( 'DataSource (3)', 'DataSource (3)'[SubjectCode] = EARLIER ( [SubjectCode] ) && 'DataSource (3)'[CreationDate] <= EARLIER ( [CreationDate] ) && 'DataSource (3)'[UserID] = EARLIER ( [UserID] ) && 'DataSource (3)'[IsStartDate] = "0" ) ) VAR _count = COUNTROWS ( FILTER ( 'DataSource (3)', 'DataSource (3)'[SubjectCode] = EARLIER ( [SubjectCode] ) && 'DataSource (3)'[CreationDate] <= EARLIER ( [CreationDate] ) && [CreationDate] >= last_0 && [UserID] = EARLIER ( [UserID] ) && [Status] = "Valid" ) ) RETURN IF ( [Status] = "Invalid", BLANK(), _count )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
