Forum Discussion

LexN's avatar
LexN
Frequent Visitor
4 years ago

Count each occurence based on a timeframe

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...
Need Help
LexN's avatar
LexN
Frequent Visitor
4 years ago

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's avatar
v-kkf-msft
Icon for Community Support rankCommunity Support
4 years ago

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.