Count the number of unpaid bills in a row since last 0

[Dubfelix]

Hi All,

I am working with transactional data.  I have two years worth of data per account.  Each Bill is a row and has a date and a column with zero if the bill is paid or a 1 to say its being unpaid.  I would like to find out the last number of bills unpaid in a row. number of rows since last 0 based on the account id.

Thanks 

Account ID Bill Date       Paid Bill ID

100000001 01-02-2013   1     1

100000001 01-04-2013   0     2

100000001 01-05-2013   1     3

100000001 01-06-2013   1     4

100000001 01-07-2013   1     5

100000002 01-02-2013   0     1

100000002 01-04-2013   1     2

100000002 01-05-2013   0     3

100000002 01-06-2013   1     4

100000002 01-07-2013   1     5

So Account ID 100000002 should be 2 and So Account ID 100000001 should be 3

[amitchandak]

@Dubfelix ,based on what I got

a measure is

calculate(count(Table[Account ID]), filter(Table, Table[Paid] =1))

sub total measure

calculate(count(Table[Account ID]), filter(Table, Table[Paid] =1), allexcept(Table, Table[Account ID])

new column

countx(filter(Table, Table[Account ID] = earlier(Table[Account ID]) && Table[Paid] =1 ), [Account ID])

[Dubfelix]

@amitchandak It seems to be doing a full count of all unpaid bills rather then unpaid since the last paid bill.  

So Account ID 100000002 should be 2 and So Account ID 100000001 should be 3

I seem to be getting based on your help

Account ID 100000002 getting 3 and So Account ID 100000001 and I getting 4

[Anonymous · Accepted solution]

Hi @Dubfelix 

I use your data model to have a test. You can try my measure.

Measure = 
VAR _Max0Date = MAXX(FILTER(ALL('Table'),AND('Table'[Account ID]=MAX('Table'[Account ID]),'Table'[Paid] = 0)),'Table'[Bill Date])
VAR _Count = CALCULATE(COUNT('Table'[ID]),FILTER(ALL('Table'),AND('Table'[Account ID]=MAX('Table'[Account ID]),'Table'[Bill Date]>_Max0Date)))
RETURN
_Count

Count = SUMX(SUMMARIZE('Table','Table'[Account ID]),[Measure])

Result is as below.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.