cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
markc2018 Frequent Visitor
Frequent Visitor

Caseload based on start and end date sliced by Month

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?

4 REPLIES 4
Community Support Team
Community Support Team

Re: Caseload based on start and end date sliced by Month

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
markc2018 Frequent Visitor
Frequent Visitor

Re: Caseload based on start and end date sliced by Month

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

Mark17/02/2017 00:00:0009/08/2018 00:00:00
Stuart28/03/2017 00:00:0014/10/2017 00:00:00
David03/04/2017 00:00:0025/07/2018 00:00:00
John28/06/2017 00:00:0028/06/2017 00:00:00
Kelly04/01/2017 00:00:0004/01/2017 00:00:00
sSimon20/03/2018 00:00:0013/04/2018 00:00:00
Jack18/06/2018 00:00:0024/06/2018 00:00:00
Sally06/02/2018 00:00:0006/02/2018 00:00:00
Vicky12/06/2018 00:00:0013/06/2018 00:00:00
Ruth30/11/2017 00:00:0024/01/2018 00:00:00

 

 

markc2018 Frequent Visitor
Frequent Visitor

Re: Caseload based on start and end date sliced by Month

This is what I need to output;

 

excel.PNG

 

Community Support Team
Community Support Team

Re: Caseload based on start and end date sliced by Month

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.