However, I'm not getting the correct results. The matrix looks the way I want it to, but it is missing any values which don't begin on or span the first day of the month. How can I make it display values which fall at any point in the month, not just on the first day of the month? I would appreciate any help anyone can give me. Thank you!
Subtracting a date from a date in M (Power Query's language) creates a duration and a duration cannot be added to a number (+1). But you can convert a duration to a number using the function Number.From. Hope this finally is the last brick to build your house.
I started with a table from MS Project Web App called Tasks, then created a calendar and crossjoined it to the Tasks table to create a table called Table. All data is now being pulled from from this Table.
I'm trying to allocate the cost into monthly buckets. No date, just month and year. I think that's where I'm messed up. My dates are showing the first of each month, so if the cost isn't on the first or straddling the first of a month, it doesn't show. I'd like it to look like this, but without the day...just all costs that would occur in May, all costs in June, etc.
Desired results, but without consideration of the day of the month
ENDOFMONTH is a time-intelligence function and will only work correctly on a proper Date table. You cannot use it on a column in any other table. If you do, either you'll get an error or the returned value will be unreliable.
Mate, first of all, you should have a Date table that spans whole years that can be found in the Task[TaskStartDate] and Task[TaskEndDate] columns. This Date table should be disconnected from the table but you should mark it as a Date table in the model so that time-intel works correctly. The Date table should have all the columns that you can find in a proper Date table (please find out how to build a good Date table for DAX's needs). You can then slice by months, days, weeks, semesters, years... Whatever you've defined in your columns.
Secondly, what happens if you have a task that starts on 2019-06-15 and ends on 2019-07-10 and you want to calculate the allocation for the month of June and for the month of July? Should it allocate the total amount linearly (call the amount K), according to:
allocation in June -> K * (30 June - 15 June + 1) / (10 July - 15 June + 1)
allocation in July -> K * (10 July - 30 June ) / (10 July - 15 June + 1)
If this is the case, then you can calculate the allocations as follows:
Budget = var __periodStart = MIN ( Dates[Date] ) var __periodEnd = MAX ( Dates[Date] ) var __allocationHelper = CALCULATETABLE ( ADDCOLUMNS( T, "Allocation", var __daysInPeriod = MAX( 0, MIN ( T[Finish], __periodEnd ) - MAX ( T[Start], __periodStart ) + 1 ) var __totalDuration = T[Finish] - T[Start] + 1 return T[Cost] * DIVIDE( __daysInPeriod, __totalDuration ) ), T[Cost] > 0 ) var __allocation = SUMX( __allocationHelper, [Allocation] ) return __allocation
But, honestly, you should do something else... You should change the data model. For each of the tasks you should create as many rows as there are days between the start and finish dates and allocate the amount to each day; so that would add 2 columns to your design: AllocationDate and AllocatedAmount. For each day, the amount would be K * 1/(finish - start + 1). Then, you should connect the Date table to the AllocatedDate column in a many-to-one fashion. What do you achieve through this? Well, you can now create this measure:
Budget = SUM ( T[AllocatedAmount] )
and be done with it. This is the best model you can have and it'll be blazingly fast.
Can you see the elegance and simplicity of the solution for the RIGHT DATA MODEL? You know what to do now.