Solving a tricky time intelligence Issue

[gambleave]

Hi experts, 

I've been trying to approximate something that had me stumped at work (below is a mock-up of the model with Power Pivot...not sure why it has a dotted link from the date table, pls disregard!).  Would be so grateful for help. 

The scenario is that teams of consultants provide service to clients. 

- ServiceHistory is the summary of interaction records

- ConsultantDirectory should be self-explanatory

Clients either pay by transaction or they may purchase an unlimited service plan for specific consultants for a certain period. 

ServicePlanRecords is a summary of service plan purchases for different consultants. A record for a consultant indicates a purchase and absence of a record implies no purchase.  These orders are periodically received as batches from clients, with the column "Received Date" indicate when that batch of orders was received. The plan would remain active until being superceded by a subsequent batch of orders.  I know it's stupid that there isn't a defined plan period... it's just the period between received dates. 

Based on this, I have a few working measures:

Count Interactions =calculate(DISTINCTCOUNT(ServiceHistory[Activity ID])) >>count total # service interactions

12M Distinct Service=

VAR endDate = TODAY()
VAR startDate=EDATE(endDate,-12)
RETURN
CALCULATE([Count Interactions],

ALL(SERVICEHISTORY[Activity Date]),

(ServiceHistory[Activity Date] >= startDate

&& (ServiceHistory[Activity Date] <= endDate))

Active Service.    >> return 1 if service recorded within 12 months, 0 if none

=if([12M Distinct Service]>=1,1,0)

Plan Count

=COUNTA(ServicePlanRecords[Consultant])

Plan Binary  >> return 1 if plan purchased for consultant, 0 if none

=if([Plan Count]>=1,1,0)

What I want to do is to chart time series by consultant/team/region, showing:

#Active service accounts on an active service plan at the time

so Active Service = 1 and Plan Binary = 1 (based on the SericePlanRecords with the latest Received Dates preceding that date)

...and other variations such as  #Active service accounts not on an active service plan

So how to set up a measure that will reference the ServicePlanRecords associated with the correct Received Date? 

[TomMartens]

Hey @gambleave ,

unfortunately it's not that simple to disregard the dotted line between the two tables: datetable and ServicePlanRecords.
The most crucial part to get all the insights from your data is the data model. For this reason please check the relationship, from my experience it's odd that the datetable is on the many side of the relationship. My assumption is that something went wrong during the creation of the relationship. If this is the case, maybe you and toggle the checkbox "".

Then please check if all your DAX is making use of this new relationship.

Consider createing a pbix with sample data, but still reflecting your data model (tables, relationships, calculated columns, and measures). Upload the pbix to onedrive, google drive, or dropbox and share the link. If you are using Excel to create the sample data instead of the manual input method share the xlsx as well.

Regards,

Tom 

[gambleave]

Thanks.. I was just trying to approximate the data model using Power Pivot as I don't have PowerBI or access to the actual project from home. Maybe I didn't set it up correctly, but there was no such issue with use of the Date table in the actual project. 

I was hoping to figure out how to have a DAX measure that will reference the most recent Received Date prior to current date.