Count each occurence based on a timeframe

[LexN]

Hello, everyone

I need some help with a formula that is above my current knowledge. If anyone could point me to the right direction, it would be much appreciated. 

Bassically I need to have a calculated column with the number of each iterration from a certain timeframe (5 days from the oldest date in that batch). On top of this, for cases where we have only 1 iterration in the 5 days timeframe, we should have "0" on the CaseCount column.

I tried various formulas, mostly using EARLIER, but I encountered some issues, the formula became too messy and I decided to drop it, now I am testing some other formulas using VAR, but with no succes.

I am attaching a table with minimum data, and different scenarios. The formula in the CaseCount column is:

CaseCount = 
VAR TimeLimit = 5
VAR UserID = DataSource[UserID]
VAR SubjectCode = DataSource[SubjectCode]
VAR CaseDate = DataSource[CreationDate]
VAR Exceptions = DataSource[Status]

VAR CaseCount=CALCULATE(COUNTROWS(DataSource),
    FILTER(DataSource, 
        UserID = DataSource[UserID] && 
        SubjectCode = DataSource[SubjectCode] && 
        CaseDate >= DataSource[CreationDate] && 
        Exceptions = "Valid"
))

RETURN CaseCount

SubjectCode	CreationDate	UserID	Status	CaseCount	Desired CaseCount
1111	2/4/2022 11:48	111111	Valid	1	1
1111	2/5/2022 16:29	111111	Valid	2	2
1111	2/5/2022 17:39	111111	Valid	3	3
1111	2/10/2022 12:07	111111	Valid	4	1
1111	2/13/2022 13:09	111111	Valid	5	2
1111	2/14/2022 14:05	111111	Invalid
1111	2/14/2022 19:48	111111	Valid	7	3
1111	2/15/2022 09:05	111111	Valid	8	4
1111	2/15/2022 16:03	111111	Valid	9	0
2222	2/4/2022 11:48	111111	Valid	1	1
2222	2/5/2022 16:29	111111	Valid	2	2
2222	2/5/2022 17:39	111111	Valid	3	3
3333	2/4/2022 11:48	111111	Valid	1	1
3333	2/5/2022 16:29	111111	Valid	2	2
3333	2/5/2022 17:39	111111	Valid	3	3
3333	2/10/2022 12:07	111111	Valid	4	0
3333	2/15/2022 13:09	111111	Valid	5	1
3333	2/16/2022 19:48	111111	Valid	6	2
3333	2/17/2022 09:05	111111	Valid	7	3
3333	2/17/2022 16:03	111111	Valid	8	4
4444	2/4/2022 11:48	111111	Valid	1	0
4444	2/15/2022 16:29	111111	Valid	2	0
1111	2/5/2022 11:48	222222	Valid	1	1
1111	2/6/2022 16:29	222222	Valid	2	2
1111	2/6/2022 17:39	222222	Valid	3	3
1111	2/11/2022 12:07	222222	Valid	4	1
1111	2/14/2022 13:09	222222	Valid	5	2
1111	2/15/2022 14:05	222222	Invalid
1111	2/15/2022 19:48	222222	Valid	7	3
1111	2/16/2022 09:05	222222	Valid	8	4
1111	2/16/2022 16:03	222222	Valid	9	0
2222	2/5/2022 11:48	222222	Valid	1	1
2222	2/6/2022 16:29	222222	Valid	2	2
2222	2/6/2022 17:39	222222	Valid	3	3
3333	2/5/2022 11:48	222222	Valid	1	1
3333	2/6/2022 16:29	222222	Valid	2	2
3333	2/6/2022 17:39	222222	Valid	3	3
3333	2/11/2022 12:07	222222	Valid	4	0
3333	2/16/2022 13:09	222222	Valid	5	1
3333	2/17/2022 19:48	222222	Valid	6	2
3333	2/18/2022 09:05	222222	Valid	7	3
3333	2/18/2022 16:03	222222	Valid	8	4
4444	2/5/2022 11:48	222222	Valid	1	0
4444	2/16/2022 16:29	222222	Valid	2	0

As I said, any help about how this can be done would be a life saver.

Thank you

[v-kkf-msft]

Hi @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.

[LexN]

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.