cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
djheathy
Helper I
Helper I

Active records over time based on Criteria

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



1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

 

Best
D

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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

 

Best
D

Anonymous
Not applicable

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

D

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.

Anonymous
Not applicable

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

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!