how many employees with intake procedure had jobcoaching procedure

[Hennie_Ergon]

We have a client follow up systeem. In that system, each client as 1 or multiple cases. For instance, all clients start with an intake case (contains CaseTypeID, clientID, startdate, enddate). Some of those clients go to a next case, e.g. job training. (CaseTypeID, ClientID, startdate, enddate), some do not.

So I have 4 tables: Clients (CLientID, name, etc),  CaseType (CaseTypeID, Casetypedescription, etc), and clientcases(clientID, CaseTypeID, date). Casetypes can be Intake, Job training)

Now in PowerBI, I am able to determine the number of Intakes per month, the number of Job trainings per month, but how do I determine the number ended jobtrainings in a month from clients that had the intake that month?

E.g.  from the number of jobttrainings in May 2020, part had an intake in april.

And the from the clients that had an intake in May, some wil have the training in may, some in june etc.

I want to have the number of job trainings in a period, from the client that had an intake in the same period.

Goals if being able to follow each client fomr beginning to end. 

[v-jayw-msft]

Hi @Hennie_Ergon ,

It's hard to build a data model only by your description. I think this can be done with a calendar table and some date calculate functions. So can you show some sample data and expected output to us if you don't have any Confidential Information?

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Hennie_Ergon , Not very clear, But you can date table with Month, qtr etc to display.

The information you have provided is not making the problem clear to me. Can you please explain with an example.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.