cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
juliecornelius
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

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!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

Anonymous
Not applicable

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

View solution in original post

21 REPLIES 21
Stachu
Community Champion
Community Champion

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!
Thank you for the kudos 🙂

Proud to be a Super User!

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.

 

 

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!
Thank you for the kudos 🙂

Proud to be a Super User!

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 monthDesired results, but without consideration of the day of the month

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!
Thank you for the kudos 🙂

Proud to be a Super User!

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

Ideas?Dax Error.JPG

Try replacing ENDOFMONTH(Table[Column]) with EOMONTH(Table[Column], 0)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

New Dax Error.JPG

 

I tried changing to EOMONTH, but got a different error message.

 

 

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!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

Anonymous
Not applicable

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

@Anonymous  I was able to successfully figure out how to insert a row for each date between start and finish date, and I labeled that column Allocation Dates. I created another column for Allocated Amount, [Cost]*1/([Finish]-[#"Start "]+1)but I'm getting an Expression Error: We cannot apply operator + to types Duration and Number. Any ideas what I'm doing wrong?

 

Anonymous
Not applicable

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

View solution in original post

@Anonymous That was it! My report is now exactly the way I want it.  Thank you so much for bearing with me!

Anonymous
Not applicable

You see... Sometimes it's worth to take a bit of time out of life 🙂 and do things the right way they should be. Your calculations should now be as fast as possible and the logic is simple. Good luck!

 

Best

Darek

Anonymous
Not applicable

I understand you're doing it in Power Query. The error says that there's a mismatch between types. From this

 

[Cost]*1/([Finish]-[#"Start "]+1)

 

it looks like you cannot do [Finish] - [#"Start "]. These two (whatever they are) have incompatible types. You have to make sure that the operation "+/-" is allowed between the two operands in question. Since this is PQ and I don't exactly know what you're doing I'm not able to be more helpful.

 

But you're on your way to the optimal solution for sure.

 

Best

Darek

@Anonymous  It's not that I'm not listening to you...I am.  I've read your message a dozen times.  I simply don't understand it.  You're a much more advanced user than am I, and I don't know how to create rows in a model where the data is coming from Microsoft Project.  I do appreciate your time and effort, I just don't know how to do what you're telling me to do.  

Anonymous
Not applicable

OK. I've never worked with MS Project... but is it not true that you have to first import data from it into Power BI? If you do, then you should be able to use Power Query to shape your tables/data. And if you can do this, then you can do anything 🙂

 

Please, tell me if you can just import data and then use PQ to shape it. Thanks.

 

Best

Darek

I get data from Project Web App via SQL Server Database.  I can then use Power Query to shape it.

I'm afraid you've lost me! I'm pulling data from Project Web App, so I don't know how to add extra rows to the data model. I'm in a class this week and maybe this is something I'll learn.  At the moment I'm feeling a bit hopeless about getting this report built. Thank you.

Anonymous
Not applicable

I have given you a whole solution... but for some reason you don't listen 🙂

 

Best

Darek

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors