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

Spreading Budgeted Cost Across Months

I have a project which is resource loaded with manpower and fixed costs and I'm working on a report to project when we will spend the dollars. I searched this forum and found instructions to do this by creating a calendar table and crossjoining my original table and the calendar table.  (https://community.powerbi.com/t5/Desktop/Splitting-Values-between-months-of-two-dates/m-p/181278#M79...)

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!

 

Capture.JPG

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Stachu Super Contributor
Super Contributor

Re: Spreading Budgeted Cost Across Months

You are missing ', 0' as second argument in EOMONTH, and you should remove MONTH in the EOMONTH as here

<=EOMONTH(Tasks[TaskFinishDate], 0)
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
Super User

Re: Spreading Budgeted Cost Across Months

Actually, I know what's wrong... Change the expression to:

 

[Cost] / ( Number.From( [Finish] - [#"Start "] ) + 1 )

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.

 

Best

Darek

21 REPLIES 21
Stachu Super Contributor
Super Contributor

Re: Spreading Budgeted Cost Across Months

Hi, can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data?

 

As described here:

How to Get Your Question Answered Quickly 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

juliecornelius Frequent Visitor
Frequent Visitor

Re: Spreading Budgeted Cost Across Months

Thank you for your help! 

This is a sample of the data:

Task NameStartFinishCost
   Issue Req7/15/20197/16/2019$315.00
   Issue PO7/17/20197/30/2019$0.00
EE1 Fab and Delivery7/31/20191/23/2020$0.00
   Receive EE11/24/20201/24/2020$630,000.00
   Issue Req9/25/20199/26/2019$315.00
   Issue PO9/27/201910/10/2019$0.00
   EE2 Fab and Delivery10/11/20191/8/2020$0.00
   Receive EE21/9/20201/9/2020$165,000.00
   Issue Req9/25/20199/26/2019$315.00
   Issue PO9/27/201910/10/2019$0.00
  EE3 Fab and Delivery10/11/201912/9/2019$0.00
   Receive EE312/10/201912/10/2019$26,000.00
   Issue Req9/25/20199/26/2019$640.00
   Issue PO9/27/201910/10/2019$0.00
  EE4 Fab and Delivery10/11/201912/9/2019$0.00
   Receive EE412/10/201912/10/2019$230,000.00

 

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.

 

 

Stachu Super Contributor
Super Contributor

Re: Spreading Budgeted Cost Across Months

OK, that's much better. And how exactly do you want to allocate the data? from what I can see first row would look like this:

Task Name Date Cost
   Issue Req 7/15/2019 157.5
Task Name 7/16/2019 157.5

is that correct?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

juliecornelius Frequent Visitor
Frequent Visitor

Re: Spreading Budgeted Cost Across Months

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. 

Budget Forecast Results.JPGDesired results, but without consideration of the day of the month

Stachu Super Contributor
Super Contributor

Re: Spreading Budgeted Cost Across Months

you can still do it with the same method, you just need to limit the calendar to the 1st of each month, e.g. like this:

Budget = 
FILTER(
    CROSSJOIN('Table' ,FILTER('Calendar', DAY('Calendar'[Date]) = 1)), 
    'Calendar'[Date] >= DATE(YEAR('Table'[Start]), MONTH('Table'[Start]), 1)
     && 'Calendar'[Date] <= ENDOFMONTH('Table'[Finish])    
)

then you just need to spread the cost

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

juliecornelius Frequent Visitor
Frequent Visitor

Re: Spreading Budgeted Cost Across Months

Thank you! I tried that, but I get this error.

Ideas?Dax Error.JPG

Super User
Super User

Re: Spreading Budgeted Cost Across Months

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.

 

Best

Darek

Super User
Super User

Re: Spreading Budgeted Cost Across Months

OK.

 

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.

 

Best

Darek

Stachu Super Contributor
Super Contributor

Re: Spreading Budgeted Cost Across Months

Try replacing ENDOFMONTH(Table[Column]) with EOMONTH(Table[Column], 0)
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 111 members 1,691 guests
Please welcome our newest community members: