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?
Could you mean to compare two dates in different columns? If so, you could use the DATEDIFF function:
And if it could not help you solve your problem, could you please share some sample data and past your desire result if possible?
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|
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.