topic Re: Snapshot data: Newsletter subscriptions per person (or newsletter) (or total) over time in DAX Commands and Tips

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

michellepace — Mon, 21 Aug 2023 06:56:13 GMT

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

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

tamerj1 — Mon, 21 Aug 2023 08:43:17 GMT

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

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

michellepace — Mon, 21 Aug 2023 10:39:45 GMT

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.

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

tamerj1 — Mon, 21 Aug 2023 10:51:10 GMT

michellepace
Try this

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

michellepace — Mon, 21 Aug 2023 11:25:17 GMT

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

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

tamerj1 — Mon, 21 Aug 2023 11:33:52 GMT

michellepace
Check this and refer to attached sample file