Forum Discussion
Need Dynamic DAX for DatesInPeriod
Hello PBI Community,
This is my first post so I'll try my best to give as much information as I can.
I'm working with a data model that reports out subscription data. Specifically when a customer started their subscription (if they started as a trialer), the date they started paying (if converted), as well as a created_at date column that records various events related to these subscriptions (if they deactivated, reactivated, they rebilled, etc).
I am currently trying to create a measure that records the number of Active Subscriptions in our system at previous points in time. Unfortunately my data model does not save the number of active subscriptions each day so I need to write a DAX measure that will do this. I have created the following measure:
ActiveSubsPrior = VAR ActiveSubs = CALCULATE (
DISTINCTCOUNT(fs_events_aggregate[fs_subscription_id]),
NOT ISBLANK(fs_events_aggregate[start_paying_date]),
fs_events_aggregate[current_status]<>"deactivated",
fs_events_aggregate[term]="Monthly",
DATEDIFF(fs_events_aggregate[created_at], TODAY()-1, DAY) >26
)
VAR AddBackDeactivations = CALCULATE(DISTINCTCOUNT(fs_events_aggregate[fs_subscription_id]),
NOT ISBLANK(fs_events_aggregate[start_paying_date]), fs_events_aggregate[type]="deactivated",
fs_events_aggregate[term]="Monthly",
DATESINPERIOD(fs_events_aggregate[created_at],MAX(fs_events_aggregate[created_at])-1,-26,DAY),
DATESINPERIOD(fs_events_aggregate[start_paying_date],MAX(fs_events_aggregate[start_paying_date])-26,-3000,DAY))
RETURN ActiveSubs+AddBackDeactivations
Now what this measure is doing is it creates two variables. The first is called ActiveSubs. What this does is returns the number of subscribers in our system that are not currently deactivated. The last line, datediff, allows me to figure out the number of active subscribers at a particular point in time. In this example, it's up to 26 days ago.
However, the data model has an issue as it overrides a subscriber's status to deactivated (hence the "current_status" field). This means that if I were to only use the first variable, ActiveSubs, and return that -- I will lose the people who deactivated after that specific date (in this case between ~August 10th to now) who had a start_paying_date that was prior to August 10th. This is not what I want as I want to look at the total number of people on August 10th that was in the system as not deactivated.
So as a stopgap, I created a second measure, AddBackDeactivations. This measure evaluates the number of subscribers users in the last 26 days (see the first DatesInPeriod calculation) who deactivated but who became a paying subscriber prior to 26 days ago (see the second DatesInPeriod calculation).
Adding the two numbers together, I get the correct number of active subscribers at a discrete point in time.
However, i need to make this continuous. If I plot the measure on a graph I get the following which is completely wrong as the "26" is a fixed number. I need to make it dynamic, where the x-axis represents the created_at dates
If I use the measure that I created and edit every place that says "26" with the day that I want to look at, I'll always get the right answer, but I need to make this discrete measure be continous over time and represent it correctly.
How can I best do this?
2 Replies
- v-yueyunzh-msft
Community Support
Hi, Anonymous
According to your description, You want to replace your "26" fixed value, generating a dynamic value that can be modified. Right?
The following is a method to replace the value, you can refer to :
(1)We can create a “What if” parameter :
(2)We can enter the interval range and interval of the variable we want:
(3)Then it will automatically generate a slicer and a measure for us:
Parameter Value = SELECTEDVALUE('Parameter'[Parameter])
(4) We can use this measure to replace your “26” fixed value to achieve dynamic through slicer selection value.
If this method cannot help you solve the problem, you can delete your private data and provide your sample data in the form of a table or .pbix file, and describe your detailed needs, so that we can better help you solve the problem.
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- AnonymousNot applicable
Hi Aniya,
The only issue with this is that I want to plot the entire time series on a column chart using the created_at date as the time column. The slicer will still only give me discrete values depending on what date I want to look at