Date Differences between 2 dates but dynamic and based on a text column

[Alison_CT]

Hopefully I can explain this well....

I have a table of data that I need to use for a document compliance tracking report.  I'm trying to figure out the best way to model the data so that I can effectively create a "KPI" or "Scorecard" visual to show compliance per document or non-compliance as this will be used to assess my organization's compliance when audited by the state child welfare department.

All the calculations for compliance are based on program enrollment/start date, but each document has a different requirement for the filing time.  In addition, there may be multiple documents of the same type per client, say treatment plans, if a client has been in the program long enough and a treatment plan must be filed every say 60 days, but the 60 days rolls out from the date of enrollment.  Then there are multiple other documents that have different periods of time for filing but all documents roll based on initiall program enrollment. 

Unfortunately, I can't really send the data file because of HIPAA , so I hope this makes sense.  I'm just trying to figure out the best way to build this out so I can create a visual that is easy for the end user to interpret, basically like a snapshot of compliance per primary worker so supervisor can just filter and see one of their worker's cases and easily assess the compliance rate of the the documentation.

I hope I explained this adequately since I can't really provide any data since it's protected.  If this wasn't clear, please if you have further questions and I'll do my best to explain further, but any suggestions on how best to go about handling this would be welcome.  My initial instinct is to build a table that has all of the calcuations per document and tie that to my main data table, but I don't know if that's the best way to approach this.  Thanks in advance for any help/suggestions you can provide.

[Alison_CT]

I don't think you'll be able to get into my OneDrive environment, but this is essentially what my data is going to look like.   I need to determine if the appropriate documents exist per each case and whether they were filed on time.  The Med Necessity forms need to be filed within 2 weeks of intake (progstart) and the actualdate field is the date of the doc filing.  The Treatment plans and CANS have to be initially filed within 30 days of intake and then Treatment plans have to be updated every 90 days thereafter and the CANS need to be updated every 6 months.  All based on the original progstart date.  I am thinking the best way to handle is to add another table into my model that has all of the dates already calculated off of the ProgStart field, but I don't know if that's the best way to go about it, which is why I'm reaching out for suggestions.  Also, the data source is SQL Server, so that may have limitations, I've written a view in SQL, which is my main data table.

[Alison_CT]

I created a .pbix file with a small sample set of data, that effectivelt mimics what my larger report will look like and the data structure is the same. But how do I upload it here?  I don't see an option for that, am I missing something?

[AlexisOlson]

Attaching files to a comment or post appears to be a special privilege, so most folds link to a file on a cloud provider like Google Drive/Dropbox/OneDrive/SharePoint.

[AlexisOlson]

You don't have to provide any sensitive data to share an example of what you're trying to do but it's difficult to answer a question like this without files or data tables to look at. Consider creating a simplified dummy .pbix that has fake data structured like your real data.