Snapshot data: Newsletter subscriptions per person (or newsletter) (or total) over time

[michellepace]

Hello there.

RE: https://michellepace.com/pics/snapshot.pbix 

Goodness how I struggle with DAX. Can someone please help me with a DAX measure to calculate the number of newsletter subscriptions? Below in the blue box, are all the details of my data. The pink shows the measure I have tried (but it only seems to work if I select ONE subscrip_id). Basically, if I am standing on a given date (eg 20 August) for a given subscription X, then I think the DAX needs to look back through time to the last row of data for subscription X (eg 15 August). If The subscription isOn for the 15th of August, then it is on and counted as a subscription for the 15, 16, 17, 18, 19, 20 August. But of course there is only one subscription count. That is the only way I can imagine solving this... but just how to take my words and put them in DAX is quite... well beyond me right now. 

Thanks very much in advance,

Michelle

[tamerj1]

Hi @michellepace 
Not sure if I fully understand but you may check below

[michellepace]

Hello @tamerj1 . Thank you. Almost there, but not quite. Your "try2" Dax measure looks to be over counting subscriptions. I think it's because of what I see below. The subscription should all be zero from the 2023-07-02 up until today. I've added your try2 measure to:  https://michellepace.com/pics/snapshot.pbix . Any ideas how to fix this?

Thanks very much once again.

[tamerj1]

@michellepace 
Try this

[michellepace]

Hi @tamerj1 . Afriad not 😐 . The measure is now undercounting. "Try3" should have a total here of "1" (not zero). I've added your measure and uploaded to https://michellepace.com/pics/snapshot.pbix

[tamerj1 · Accepted solution]

@michellepace 
Check this and refer to attached sample file