I'm trying to use Power Pivot to extract data from a Data Model to show workload based on a start date (referral date) and end date, (Discharge Date). I want to show that on workload throughout the month so a job can last for several months but I want to show that in a monthly view. I'm quite new to DAX, and I'm assuming I need to do this with a measure, but cant see how. I've created some test data in a table and a date table and created a relationship between the date columns and that table. I just cant see how I slice the data up to show it per month. Any help greatly received.
I'm not sure I've explained this very well. What I'm trying to build is a DAX expression that will compare the start date of workload with the end date and then show that as a project in operation per month. So if a project started in March 2017 and ended in Jan 2018, I want to show this in a table split into month view.
I've created a test spreadsheet with the relevant data as the actual data model has masses of other data, so Columns 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?