Counting previous occurrences in Dax

[5foot9]

[FONT=&quot]I have a column called 'slots' containing values from 1 to 100 which populate the column any amount of times and in any order. They are sorted by date but I don't know if it's possible to use EARLIER to count previous occurrences of each slot by row as there are often multiple occurrences for each date, ideally I would like to avoid relying on dates. [/FONT]

[FONT=&quot]So I am trying to count each occurrence of a slot number row by row in a calculated column to eventually calculate sequences and frequencies.[/FONT]

[FONT=&quot]This is what I've been trying but gives me the total count of each slot number each time. If anyone is able to point me in the right direction it would be greatly appreciated.[/FONT]

[FONT=&quot][=CALCULATE(COUNT([Slot]),FILTER(Records,Records[Slot]<=EARLIER(Records[Slot])))]

[/FONT]I've posted the same post here too - PowerPivot Forum - Login

By the way I'm using powerpivot 2016

 

[gazpage]

You literally want to look at the rows above the current row in the table? As far as I am aware PowerPivot doesn't even consider the position of a row.

In order to do what you want to do you would need to add an index column to you data. You could easily do that with Power Query if needed. Then use your filter logic above with earlier to pick up the row above.

NB that you probably only want an = in the part above to look for rows with the same value in the slot column.

 

[5foot9]

Thanks for your reply, I'm new to PP and have seen a few mentions of index columns and power query, which led me here - https://powerpivotpro.com/2015/03/how-to-compare-the-current-row-to-the-previous-row-in-power-pivot/

I'm going to go for building the table in excel then adding it to the data model, this is probably the best thing to do at this stage.

 

[gazpage]

I had a quick look and he mentions that you can use EARLIER to look at the previous row, but does not show how to actually do it. His first link is just a description of EARLIER and the second is a link to his book...

You could just add an index column in excel and then bring that into PP. The Power Query way is pretty simple and I could explain that if it helped.

Once you have an index column you want something like.

CALCULATE ( 
    COUNTROWS ( Records ),
    FILTER ( ALL ( Records ),
        [COLOR=#333333]Records[Slot]=EARLIER ( Records[Slot] ) &&
        Records[Index] < EARLIER ( Records[Index]
    )
)
[/COLOR]

 

[5foot9]

Ok....I went round in circles with this late last night and gave up!....
I think sometimes when I'm faced with so much information I can't see the wood for the trees!

I added an index in excel and entered your DAX, I had to make'<=' as the original was not in the correct row. The problem I found then was it was picking total count of slot, not just losers which is what I actually want! I've added that in as you can see below and ideally I would like the blanks populated with 0 but what its doing now is just counting all the losers and not giving me streaks or sequences.

=CALCULATE (     COUNTROWS ( Records ),
    FILTER ( ALL ( Records ),
        Records[Slot]=EARLIER ( Records[Slot] ) &&
        EARLIER(Records[Lost])=1 && Records[Index] <= EARLIER ( Records[Index]
    )
))

I have made this work in an excel table where the result triggers a reset in the sequence - I don't know if this is possible with PP.
I don't have permission to post attachments unfortunately hopefully this will do -

[TABLE="width: 839"]
<colgroup><col width="75" style="width:56pt"> <col width="64" style="width:48pt" span="2"> <col width="99" style="width:74pt"> <col width="64" style="width:48pt"> <col width="71" style="width:53pt" span="2"> <col width="75" style="width:56pt"> <col width="64" style="width:48pt" span="4"> </colgroup><tbody>[TR]
[TD="class: xl317, width: 437, colspan: 6"]EXCEL[/TD]
[TD="class: xl154, width: 71"][/TD]
[TD="class: xl307, width: 331, colspan: 5"]POWER PIVOT[/TD]
[/TR]
[TR]
[TD="class: xl366"]Date[/TD]
[TD="class: xl366"]ID[/TD]
[TD="class: xl366"]slot[/TD]
[TD="class: xl366"]count of slot[/TD]
[TD="class: xl366"]Lost[/TD]
[TD="class: xl368"]LossSeq[/TD]
[TD="class: xl154"][/TD]
[TD="class: xl369"]Date[/TD]
[TD="class: xl370"]index[/TD]
[TD="class: xl371"]slot[/TD]
[TD="class: xl371"]Lost[/TD]
[TD="class: xl371"]SeqTest[/TD]
[/TR]
[TR]
[TD="class: xl372"]12/04/2017[/TD]
[TD="class: xl373"]11-370[/TD]
[TD="class: xl373"]11[/TD]
[TD="class: xl373"]370[/TD]
[TD="class: xl373"]1[/TD]
[TD="class: xl374"]3[/TD]
[TD="class: xl154"][/TD]
[TD="class: xl375, width: 75"]12/04/2017[/TD]
[TD="class: xl376, width: 64"]8557[/TD]
[TD="class: xl377, width: 64"]11[/TD]
[TD="class: xl377, width: 64"]1[/TD]
[TD="class: xl377, width: 64"]370[/TD]
[/TR]
[TR]
[TD="class: xl378"]18/04/2017[/TD]
[TD="class: xl379"]11-371[/TD]
[TD="class: xl379"]11[/TD]
[TD="class: xl379"]371[/TD]
[TD="class: xl379"]0[/TD]
[TD="class: xl380"]0[/TD]
[TD="class: xl154"][/TD]
[TD="class: xl381, width: 75"]18/04/2017[/TD]
[TD="class: xl382, width: 64"]8615[/TD]
[TD="class: xl383, width: 64"]11[/TD]
[TD="class: xl383, width: 64"]0[/TD]
[TD="class: xl383, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl384"]19/04/2017[/TD]
[TD="class: xl385"]11-372[/TD]
[TD="class: xl385"]11[/TD]
[TD="class: xl385"]372[/TD]
[TD="class: xl385"]1[/TD]
[TD="class: xl386"]1[/TD]
[TD="class: xl154"][/TD]
[TD="class: xl375, width: 75"]19/04/2017[/TD]
[TD="class: xl376, width: 64"]8625[/TD]
[TD="class: xl377, width: 64"]11[/TD]
[TD="class: xl377, width: 64"]1[/TD]
[TD="class: xl377, width: 64"]372[/TD]
[/TR]
[TR]
[TD="class: xl378"]19/04/2017[/TD]
[TD="class: xl379"]11-373[/TD]
[TD="class: xl379"]11[/TD]
[TD="class: xl379"]373[/TD]
[TD="class: xl379"]1[/TD]
[TD="class: xl380"]2[/TD]
[TD="class: xl154"][/TD]
[TD="class: xl381, width: 75"]19/04/2017[/TD]
[TD="class: xl382, width: 64"]8626[/TD]
[TD="class: xl383, width: 64"]11[/TD]
[TD="class: xl383, width: 64"]1[/TD]
[TD="class: xl383, width: 64"]373[/TD]
[/TR]
[TR]
[TD="class: xl384"]19/04/2017[/TD]
[TD="class: xl385"]11-374[/TD]
[TD="class: xl385"]11[/TD]
[TD="class: xl385"]374[/TD]
[TD="class: xl385"]1[/TD]
[TD="class: xl386"]3[/TD]
[TD="class: xl154"][/TD]
[TD="class: xl375, width: 75"]19/04/2017[/TD]
[TD="class: xl376, width: 64"]8627[/TD]
[TD="class: xl377, width: 64"]11[/TD]
[TD="class: xl377, width: 64"]1[/TD]
[TD="class: xl377, width: 64"]374[/TD]
[/TR]
[TR]
[TD="class: xl378"]19/04/2017[/TD]
[TD="class: xl379"]11-375[/TD]
[TD="class: xl379"]11[/TD]
[TD="class: xl379"]375[/TD]
[TD="class: xl379"]1[/TD]
[TD="class: xl380"]4[/TD]
[TD="class: xl154"][/TD]
[TD="class: xl381, width: 75"]19/04/2017[/TD]
[TD="class: xl382, width: 64"]8628[/TD]
[TD="class: xl383, width: 64"]11[/TD]
[TD="class: xl383, width: 64"]1[/TD]
[TD="class: xl383, width: 64"]375[/TD]
[/TR]
[TR]
[TD="class: xl384"]20/04/2017[/TD]
[TD="class: xl385"]11-376[/TD]
[TD="class: xl385"]11[/TD]
[TD="class: xl385"]376[/TD]
[TD="class: xl385"]1[/TD]
[TD="class: xl386"]5[/TD]
[TD="class: xl154"][/TD]
[TD="class: xl375, width: 75"]20/04/2017[/TD]
[TD="class: xl376, width: 64"]8633[/TD]
[TD="class: xl377, width: 64"]11[/TD]
[TD="class: xl377, width: 64"]1[/TD]
[TD="class: xl377, width: 64"]376[/TD]
[/TR]
[TR]
[TD="class: xl378"]20/04/2017[/TD]
[TD="class: xl379"]11-377[/TD]
[TD="class: xl379"]11[/TD]
[TD="class: xl379"]377[/TD]
[TD="class: xl379"]1[/TD]
[TD="class: xl380"]6[/TD]
[TD="class: xl154"][/TD]
[TD="class: xl381, width: 75"]20/04/2017[/TD]
[TD="class: xl382, width: 64"]8634[/TD]
[TD="class: xl383, width: 64"]11[/TD]
[TD="class: xl383, width: 64"]1[/TD]
[TD="class: xl383, width: 64"]377[/TD]
[/TR]
[TR]
[TD="class: xl384"]21/04/2017[/TD]
[TD="class: xl385"]11-378[/TD]
[TD="class: xl385"]11[/TD]
[TD="class: xl385"]378[/TD]
[TD="class: xl385"]1[/TD]
[TD="class: xl386"]7[/TD]
[TD="class: xl154"][/TD]
[TD="class: xl375, width: 75"]21/04/2017[/TD]
[TD="class: xl376, width: 64"]8639[/TD]
[TD="class: xl377, width: 64"]11[/TD]
[TD="class: xl377, width: 64"]1[/TD]
[TD="class: xl377, width: 64"]378[/TD]
[/TR]
[TR]
[TD="class: xl378"]22/04/2017[/TD]
[TD="class: xl379"]11-379[/TD]
[TD="class: xl379"]11[/TD]
[TD="class: xl379"]379[/TD]
[TD="class: xl379"]1[/TD]
[TD="class: xl380"]8[/TD]
[TD="class: xl154"][/TD]
[TD="class: xl381, width: 75"]22/04/2017[/TD]
[TD="class: xl382, width: 64"]8652[/TD]
[TD="class: xl383, width: 64"]11[/TD]
[TD="class: xl383, width: 64"]1[/TD]
[TD="class: xl383, width: 64"]379[/TD]
[/TR]
[TR]
[TD="class: xl384"]22/04/2017[/TD]
[TD="class: xl385"]11-380[/TD]
[TD="class: xl385"]11[/TD]
[TD="class: xl385"]380[/TD]
[TD="class: xl385"]1[/TD]
[TD="class: xl386"]9[/TD]
[TD="class: xl154"][/TD]
[TD="class: xl375, width: 75"]22/04/2017[/TD]
[TD="class: xl376, width: 64"]8653[/TD]
[TD="class: xl377, width: 64"]11[/TD]
[TD="class: xl377, width: 64"]1[/TD]
[TD="class: xl377, width: 64"]380[/TD]
[/TR]
[TR]
[TD="class: xl378"]25/04/2017[/TD]
[TD="class: xl379"]11-381[/TD]
[TD="class: xl379"]11[/TD]
[TD="class: xl379"]381[/TD]
[TD="class: xl379"]0[/TD]
[TD="class: xl380"]0[/TD]
[TD="class: xl154"][/TD]
[TD="class: xl381, width: 75"]25/04/2017[/TD]
[TD="class: xl382, width: 64"]8674[/TD]
[TD="class: xl383, width: 64"]11[/TD]
[TD="class: xl383, width: 64"]0[/TD]
[TD="class: xl383, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl384"]29/04/2017[/TD]
[TD="class: xl385"]11-382[/TD]
[TD="class: xl385"]11[/TD]
[TD="class: xl385"]382[/TD]
[TD="class: xl385"]1[/TD]
[TD="class: xl386"]1[/TD]
[TD="class: xl154"][/TD]
[TD="class: xl375, width: 75"]29/04/2017[/TD]
[TD="class: xl376, width: 64"]8706[/TD]
[TD="class: xl377, width: 64"]11[/TD]
[TD="class: xl377, width: 64"]1[/TD]
[TD="class: xl377, width: 64"]382[/TD]
[/TR]
[TR]
[TD="class: xl378"]30/04/2017[/TD]
[TD="class: xl379"]11-383[/TD]
[TD="class: xl379"]11[/TD]
[TD="class: xl379"]383[/TD]
[TD="class: xl379"]1[/TD]
[TD="class: xl380"]2[/TD]
[TD="class: xl154"][/TD]
[TD="class: xl381, width: 75"]30/04/2017[/TD]
[TD="class: xl382, width: 64"]8707[/TD]
[TD="class: xl383, width: 64"]11[/TD]
[TD="class: xl383, width: 64"]1[/TD]
[TD="class: xl383, width: 64"]383[/TD]
[/TR]
[TR]
[TD="class: xl384"]30/04/2017[/TD]
[TD="class: xl385"]11-384[/TD]
[TD="class: xl385"]11[/TD]
[TD="class: xl385"]384[/TD]
[TD="class: xl385"]1[/TD]
[TD="class: xl386"]3[/TD]
[TD="class: xl154"][/TD]
[TD="class: xl375, width: 75"]30/04/2017[/TD]
[TD="class: xl376, width: 64"]8708[/TD]
[TD="class: xl377, width: 64"]11[/TD]
[TD="class: xl377, width: 64"]1[/TD]
[TD="class: xl377, width: 64"]384[/TD]
[/TR]
[TR]
[TD="class: xl378"]01/05/2017[/TD]
[TD="class: xl379"]11-385[/TD]
[TD="class: xl379"]11[/TD]
[TD="class: xl379"]385[/TD]
[TD="class: xl379"]0[/TD]
[TD="class: xl380"]0[/TD]
[TD="class: xl154"][/TD]
[TD="class: xl381, width: 75"]01/05/2017[/TD]
[TD="class: xl382, width: 64"]8716[/TD]
[TD="class: xl383, width: 64"]11[/TD]
[TD="class: xl383, width: 64"]0[/TD]
[TD="class: xl383, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl384"]01/05/2017[/TD]
[TD="class: xl385"]11-386[/TD]
[TD="class: xl385"]11[/TD]
[TD="class: xl385"]386[/TD]
[TD="class: xl385"]1[/TD]
[TD="class: xl386"]1[/TD]
[TD="class: xl154"][/TD]
[TD="class: xl375, width: 75"]01/05/2017[/TD]
[TD="class: xl376, width: 64"]8717[/TD]
[TD="class: xl377, width: 64"]11[/TD]
[TD="class: xl377, width: 64"]1[/TD]
[TD="class: xl377, width: 64"]386[/TD]
[/TR]
[TR]
[TD="class: xl378"]03/05/2017[/TD]
[TD="class: xl379"]11-387[/TD]
[TD="class: xl379"]11[/TD]
[TD="class: xl379"]387[/TD]
[TD="class: xl379"]1[/TD]
[TD="class: xl380"]2[/TD]
[TD="class: xl154"][/TD]
[TD="class: xl381, width: 75"]03/05/2017[/TD]
[TD="class: xl382, width: 64"]8727[/TD]
[TD="class: xl383, width: 64"]11[/TD]
[TD="class: xl383, width: 64"]1[/TD]
[TD="class: xl383, width: 64"]387[/TD]
[/TR]
[TR]
[TD="class: xl384"]03/05/2017[/TD]
[TD="class: xl385"]11-388[/TD]
[TD="class: xl385"]11[/TD]
[TD="class: xl385"]388[/TD]
[TD="class: xl385"]1[/TD]
[TD="class: xl386"]3[/TD]
[TD="class: xl154"][/TD]
[TD="class: xl375, width: 75"]03/05/2017[/TD]
[TD="class: xl376, width: 64"]8728[/TD]
[TD="class: xl377, width: 64"]11[/TD]
[TD="class: xl377, width: 64"]1[/TD]
[TD="class: xl377, width: 64"]388[/TD]
[/TR]
[TR]
[TD="class: xl387"]05/05/2017[/TD]
[TD="class: xl388"]11-389[/TD]
[TD="class: xl388"]11[/TD]
[TD="class: xl388"]389[/TD]
[TD="class: xl388"]1[/TD]
[TD="class: xl389"]4[/TD]
[TD="class: xl154"][/TD]
[TD="class: xl390, width: 75"]05/05/2017[/TD]
[TD="class: xl391, width: 64"]8739[/TD]
[TD="class: xl392, width: 64"]11[/TD]
[TD="class: xl392, width: 64"]1[/TD]
[TD="class: xl392, width: 64"]389[/TD]
[/TR]
</tbody>[/TABLE]

 

[gazpage]

You can make a measure that returns a blank return a 0 instead by simply adding + 0 to it.