Point-in-time for a change log

[abcd]

Hello,

My organisation has a subscription based model where customers subscribe to us. I would like a measure that calculates how many subscribers we have on a given date. The complicating factor is that someone can subscribe, unsubscribe, and then resubscribe again - and will maintain the same customer ID. This is not uncommon.

The data is like this:

- CalendarAuto generated date table

- Table called ins_outs that is like this:

Customer ID	Change Date	Old Status	New Status
eg 12345	15/3/21	Subscribed	Unsubscribed
11111	13/3/21	Unsubscribed	Subscribed
22222	11/3/21	NULL (if entering for first time)	Subscribed

So what I need is a measure that will use this sort of logic:

for a selected date x (using slicer)

the most recent change that happened before x is to suscribe AND there are no changes after x

I can't figure out how to get this 'most recent change' logic partitioned over customer ID

Can anyone help? Thanks in advance!

[amitchandak]

@abcd , Create a measure like this are try

Measure =
VAR __id = MAX ('Table'[Customer ID] )
VAR __date = CALCULATE ( MAX('Table'[Change Date] ), ALLSELECTED ('Table' ), 'Table'[Customer ID] = __id )
countx( values(table[Customer ID]),if( CALCULATE ( max ('Table'[New Status] ), VALUES ('Table'[Customer ID] ),'Table'[Customer ID] = __id,'Table'[Change Date] = __date ) = "Subscribed", [Customer ID], blank()))

[abcd]

Hi! Thanks for replying. I tried this, but I don't think the IF statement is behaving correctly. Why are we taking max member id? It seems to be looking at whatever that max row is - if it's "Subscribed" it'll return the count of ALL the rows from that day (including the unsubs) and if it's "Unsubcribed" it returns blank.