topic Active records over time based on Criteria in DAX Commands and Tips

Active records over time based on Criteria

djheathy — Mon, 27 Apr 2020 13:48:24 GMT

I have a services table (Fact) and a Service Type (Dimension) table. Each service has a cost, a start and end date, which is linked to a customer and a service type. I also have an active flag on the Services table where the service is currently active.
Each customer can have many active services at once.

I've created a couple of calculations to show a count of customers with active services over time:

Number of active customers =

CALCULATE(
DISTINCTCOUNT('Services'[Person_ID])
,Filter('Services','Services'[START_DTTM]<=now())
,FILTER('Services','Services'[END_DTTM]>=now())

)

and which shows the active users with services of a specific type over $600

users with total Services $600+ =

Calculate(
DISTINCTCOUNT('Services'[PERSON_ID]),
FILTER('Services','Services'[Active]=1),

Filter('Services',(
CALCULATE(sum('Services'[Cost]),
ALLEXCEPT('Services',
'Services'[PERSON_ID],
'Services'[Active],
'Service Type'[ServiceGroup]
)
))>600
)

)

Now, I want to combine the two. However, if I add the filter from the first calculation into the second, I need to add start date and end date into the ALLEXCEPT clause, however, this then only groups together services that start and end on the same day, which doesn't give me the desired result. Any help would be great. Thanks

Re: Active records over time based on Criteria

Anonymous — Mon, 27 Apr 2020 14:17:40 GMT

Hi there.

Hard-coding values (now()) into measures like this:

Number of active customers = CALCULATE( DISTINCTCOUNT('Services'[Person_ID]) ,Filter('Services','Services'[START_DTTM]<=now()) ,FILTER('Services','Services'[END_DTTM]>=now()) )

is not how it should be done. The way you've done it prevents you from showing the number of active customers over time since the conditions are fixed at today's date instead of being definable by the user.

Best

Re: Active records over time based on Criteria

djheathy — Mon, 27 Apr 2020 14:59:43 GMT

Hi Anonymous

Apologies I wasn't too clear. The piece of code that you quoted was just where I have calculated currently active services. To see them over time, I have tried something like this:

Calculate(
DISTINCTCOUNT('Services'[PERSON_ID]),
FILTER('Services','Services'[START_Date]<=LASTDATE('Date'[Date])&&[END_Date]>=LASTDATE('Date'[Date])),

Filter('Services',(
CALCULATE(sum('Services'[COST]),
ALLEXCEPT(
'Services',
'Services'[PERSON_ID],
'Service Type'[ServiceGroup])))>600)

)
However, this doesn't work as the ALLEXCEPT clears the dynamic date filter used to see the count over time. If I add START_Date and END_Date into the ALLEXCEPT function then it only groups services where the start and end date are the same.

Re: Active records over time based on Criteria

Anonymous — Mon, 27 Apr 2020 15:53:36 GMT

That's because you need a disconnected date table to show the number of active services or the number of customers with active services. A connected table will not achieve what you need.

Best
D

Re: Active records over time based on Criteria

djheathy — Mon, 27 Apr 2020 16:25:31 GMT

The date table is disconnected, so it works over time.

However, I think the problem lies in the fact that I need to add the a dynamic 'active' flag into the ALLEXCEPT function, because I need to group all the active services to see which customers have over $600 at the time of the calculation.

Is there a way I can do that in a variable and then add the result of the variable into the ALLEXCEPT function?

Something like this:

Active users >$600 over time=

Var
ACTIVEUSERS= calculate(1,FILTER('Services','Services'[START_Date]<=LASTDATE('Date'[Date])&&[END_Date]>=LASTDATE('Date'[Date])))

RETURN

Calculate(
DISTINCTCOUNT('Services'[PERSON_ID]),

Filter('Services',(
CALCULATE(sum('Services'[COST]),
ALLEXCEPT(
'Services',
'Services'[PERSON_ID],
'Service Type'[ServiceGroup],

ACTIVEUSERS

)))>600)

)

I know this is wrong, but hopefully you can logically see where I'm trying to go with this

Re: Active records over time based on Criteria

Anonymous — Mon, 27 Apr 2020 22:43:06 GMT

Check out the file. No field "Service Active" required since this can be deduced from the start and end dates.

Best
D