Reset count of ID once they've all been seen, count how many days it took then reset until next time

[Syndicate_Admin]

Hi,

I have 1500 unique IDs, everyday many are seen and some multiple times a day.

I would like to do a cumulative count and know how many days does it takes to see them all 1500, once it reaches that amount, the cumulative sum needs to reset for another count until the next time the goal is met.

I've done a running total after a grouped count by date, but my issue aside from the needed reset is that the duplicates needs to be removed based on the time period needed to attain the goal. If i'm grouping my IDs by date then i'm losing the ability to sort out duplicates...

So i'm guessing I need to first find how much time is needed, then remove the duplicates from a custom column spanning over its own run tower the goal, and then, calculate the running...

Here's an ugly paint mockup of what i'd like my final result to look like (I realize I need my date format to be date.time otherwise i'll never get a correct goal and it'll be tricky)

I've found this solution here from Lind25 but the syntax is wrong, maybe from a previous PQ version:

https://community.powerbi.com/t5/Desktop/Running-total-that-resets-when-equal-to-or-greater-than-a-s...

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddedIndex = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(AddedIndex, "Running total reset", each List.Accumulate(List.FirstN(AddedIndex[Difference],[Index]),0,(state,current)=>if state+current > 60 then 0 else state+current))
in
    #"Added Custom"

Any takers ? I'm completely lost on that one...

Thanks

[Syndicate_Admin]

@AlB  You are right.  I will think more on a potential non-recursive DAX approach, but it may not be possible.  

Regards,

Pat

[Syndicate_Admin]

Hi @ziying35 

I totally agree with you. The logic depends on the specifications.
I don't know if @Kiwizqt  thought of a situation like the one you showed.
If it is important for him to distinguish situations, he can choose between red (mine) and blue (yours).

I just point out that the solution you intended may leave some transactions out of each completion block In this case the row n.90) , which I don't know is important or irrelevant.

[Syndicate_Admin]

@Rocco_sprmnt21 

Earlier I posted a solution using the List.Generate function, and I've simulated every possible scenario I can think of in the solution, so you'll see where I'm going with this.

[Syndicate_Admin]

Hi @ziying35 

you are right. Your solution leaves no unassigned records for the various blocks.
I apologize for the oversight.
I applied your fix to the recursive function, but then misread the resulting table.
however, the difference in the result remains, which in some cases could be important.
At this point it must be @Kiwizqt  who decides what is preferable for its purposes.

I try to give an extreme example, to clarify the difference between the two approaches.
Correct me if I'm wrong.

If on the day a block is completed, the last block ID is present several times, you get the last one, while I get the first one.
A consequence of this different choice is that the next block, in my case, starts from the same day and already contains an occurrence of this ID; in the case of the choice made by ZiYing, the next block does not start with the same ID and therefore the completion of the block also depends on the subsequent occurrence of this ID.
Obviously there is no right or wrong solution they are just different.

[Syndicate_Admin]

@Rocco_sprmnt21 

Yes, you have understood my line of thinking perfectly. There is strictly no right or wrong solution for either of us, and the final choice will be @Kiwizqt's actual needs

[Syndicate_Admin]

@AlB  @Kiwizqt  @Rocco_sprmnt21 

FYI that I thought of a way to do this with DAX and no recursion.  See the attached pbix.  There were only two cycles in the example data, so I made a mock table too with integer date values (table on right).  I made a DAX calculated table with the start date of each cycle, and it would be easy to get the desired measures from it.  It would also be possible to put this expression in a variable in a measure and calculate it dynamically (not sure how performant it will be at large scale, and doing it as a table was easier to troubleshoot).  Here is the DAX expression that appears to work.  Note the upper limit of the GENERATESERIES function should be changed to the max potential # of cycles).

CycleStarts2 = 
VAR counts =
    ADDCOLUMNS (
        CROSSJOIN (
            VALUES ( Seen2[Date] ),
            VALUES ( Seen2[ID] )
        ),
        "count",
            VAR thisDate = Seen2[Date]
            RETURN
                CALCULATE (
                    COUNT ( Seen2[ID] ),
                    ALL ( Seen2[Date] ),
                    Seen2[Date] <= thisDate
                ) + 0
    )
VAR mincounts =
    ADDCOLUMNS (
        SUMMARIZE (
            counts,
            Seen2[Date]
        ),
        "mincount",
            VAR thisDate = Seen2[Date]
            RETURN
                MINX (
                    FILTER (
                        counts,
                        Seen2[Date] = thisDate
                    ),
                    [count]
                )
    )
VAR cycles2 =
    ADDCOLUMNS (
        GENERATESERIES (
            1,
            4,
            1
        ),
        "StartOfNewCycle",
            MINX (
                FILTER (
                    mincounts,
                    [mincount] = [Value]
                ),
                Seen2[Date]
            )
    )
RETURN
    cycles2

Regards,

Pat

[Syndicate_Admin]

About the changing the recursive code to implement the different logic suggested by @ziying35 ,  I would like to propose a different way of obtaining the same result.
In practice, this is done:   take the last element of the first group of list of groups by day of the last group of list of groups by ID.

In M code, this becomes

let
  completion = (tab) => 
    let
      grpID = Table.Group(tab, {"ID"}, {"grp", each _}),
      nids = Table.RowCount(grpID),
      currFirstDate = Table.First(List.First(grpID[grp]), "Date"),
      currLastDate = Table.Last(
          List.First(
              Table.Group(
                  List.Last(grpID[grp]), 
                  {"Date"}, 
                  {"idByday", each _}, 
                  GroupKind.Local, 
                  (x, y) => Date.Day(x[Date]) - Date.Day(y[Date])
                )[idByday]
            ), 
          "Date"
        ),
      rest = Table.SelectRows(tab, each _[Date] > currLastDate[Date]),
      result = 
        if Table.RowCount(Table.Distinct(rest, "ID")) < nids then 
          {[Start = currFirstDate, End = currLastDate]}
        else 
          {[Start = currFirstDate, End = currLastDate]} & @completion(rest)
    in
      result
in
  completion

[Syndicate_Admin]

@mahoneypat 

This seems to yield the same erroneous result for the example I provided earlier, or am I mistaken? It is a very smart and elegant implementation but the logic for the core computation is essentially the same as before, thus carrying the same flaws. The code still looks, for cycle N,  for the earliest date on which all IDs have been seen N times since the beginning. That does work for N=1 but not for N>1.

Best

Date	ID
1	100
2	100
3	100
4	200
5	200
6	300	<--1st period
7	300	<--2nd period
8	200
9	300	<--3rd period

[Syndicate_Admin]

It did work with the set I created but it did break down again when I threw other patterns at it.  I had an extra virtual column in there that was more complex but closer to the solution I think.  When I had both, the simpler one ("mincount") also worked (which I had created as part of the 2nd one), so I got rid of the more complex one.  Below is a variation on it.  I think this is close, but the last piece of logic is stumping me.  It works by looking ahead and finding the next datetime where the running total count of each ID is different (i.e., at least +1) by using ISBLANK on the COUNTROWS of INTERSECT.

This has gotten pretty academic at this point, but here is the table expression if anyone is still following this one.  It calculates against the Seen2 table I had in previous pbix with numeric "datetime" values.  It shows when the next full cycle occurs from that point (date), but doesn't get it relative to the start (again, not recursive which may be the fundamental limitation).

CycleStarts4 =
VAR counts =
    ADDCOLUMNS (
        CROSSJOIN (
            VALUES ( Seen2[Date] ),
            VALUES ( Seen2[ID] )
        ),
        "count",
            VAR thisDate = Seen2[Date]
            RETURN
                CALCULATE (
                    COUNT ( Seen2[ID] ),
                    ALL ( Seen2[Date] ),
                    Seen2[Date] <= thisDate
                ) + 0
    )
VAR withlast =
    ADDCOLUMNS (
        ADDCOLUMNS (
            VALUES ( Seen2[Date] ),
            "mincount",
                VAR thisdate3 = Seen2[Date]
                RETURN
                    MINX (
                        FILTER (
                            counts,
                            Seen2[Date] = thisdate3
                        ),
                        [count]
                    )
        ),
        "nextcyclestart",
            VAR thisdate2 = Seen2[Date]
            VAR thismin = [mincount]
            VAR thisvalues =
                SUMMARIZE (
                    FILTER (
                        counts,
                        Seen2[Date] = thisdate2
                    ),
                    Seen2[ID],
                    [count]
                )
            VAR othervalues =
                FILTER (
                    FILTER (
                        counts,
                        Seen2[Date] > thisdate2
                    ),
                    ISBLANK (
                        VAR thisdate3 = Seen2[Date]
                        VAR prevvalues =
                            SUMMARIZE (
                                FILTER (
                                    counts,
                                    Seen2[Date] = thisdate3
                                ),
                                Seen2[ID],
                                [count]
                            )
                        RETURN
                            COUNTROWS (
                                INTERSECT (
                                    thisvalues,
                                    prevvalues
                                )
                            )
                    )
                )
            RETURN
                MINX (
                    othervalues,
                    Seen2[Date]
                )
    )
RETURN
    withlast

Regards,

Pat