Caseload based on start and end date sliced by Month

[markc2018]

Hi All. I'm trying to build a DAX expression that will compare the start date of workload with the end date and then show that as a case in operation per month. So if a case started in March 2017 and ended in Jan 2018, I want to show thi in a table split into month view. I need to be clear, I'm doing this in Excel with power pivot, but I think the DAX expression would be the same...?

I've created a test spreadsheet with the relevant data as the actual data model has masses of other data, so Colums in the test spreadsheet are, Referral Date (Start date), Discharge Date (End Date) and Project Name.

So I just want to show that "Project", lasted between the start and end date in a table carved up into months so I can see the workload each month... sounds easy...right?

[v-danhe-msft]

Hi @markc2018,

Could you mean to compare two dates in different columns? If so, you could use the DATEDIFF function:

https://docs.microsoft.com/en-us/dax/datediff-function-dax

And if it could not help you solve your problem, could you please share some sample data and past your desire result if possible?

Regards,

Daniel He

[markc2018]

This is what I need to output;

[markc2018]

Hi, so what I need to do is compare 3 dates, a start, a finnish to get the length of time the project is running for and then a third dtae that slices the data into a monthly caseload.

Just for some test data I created this basic table;

Name Referral Date Discharge Date

Mark	17/02/2017 00:00:00	09/08/2018 00:00:00
Stuart	28/03/2017 00:00:00	14/10/2017 00:00:00
David	03/04/2017 00:00:00	25/07/2018 00:00:00
John	28/06/2017 00:00:00	28/06/2017 00:00:00
Kelly	04/01/2017 00:00:00	04/01/2017 00:00:00
sSimon	20/03/2018 00:00:00	13/04/2018 00:00:00
Jack	18/06/2018 00:00:00	24/06/2018 00:00:00
Sally	06/02/2018 00:00:00	06/02/2018 00:00:00
Vicky	12/06/2018 00:00:00	13/06/2018 00:00:00
Ruth	30/11/2017 00:00:00	24/01/2018 00:00:00

[v-danhe-msft]

Hi @markc2018,

Based on my test, I am afraid you could not get this feature currently. In Power BI, I am afraid it could not list month's names between two date columns automatically.

Regards,

Daniel He