topic Re: Spreading Budgeted Cost Across Months in DAX Commands and Tips

Spreading Budgeted Cost Across Months

juliecornelius — Wed, 10 Jul 2019 20:54:48 GMT

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#M79384)

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!

Re: Spreading Budgeted Cost Across Months

Stachu — Sun, 14 Jul 2019 02:59:05 GMT

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

As described here:

Re: Spreading Budgeted Cost Across Months

juliecornelius — Mon, 15 Jul 2019 12:47:09 GMT

Thank you for your help!

This is a sample of the data:

Task Name	Start	Finish	Cost
Issue Req	7/15/2019	7/16/2019	$315.00
Issue PO	7/17/2019	7/30/2019	$0.00
EE1 Fab and Delivery	7/31/2019	1/23/2020	$0.00
Receive EE1	1/24/2020	1/24/2020	$630,000.00
Issue Req	9/25/2019	9/26/2019	$315.00
Issue PO	9/27/2019	10/10/2019	$0.00
EE2 Fab and Delivery	10/11/2019	1/8/2020	$0.00
Receive EE2	1/9/2020	1/9/2020	$165,000.00
Issue Req	9/25/2019	9/26/2019	$315.00
Issue PO	9/27/2019	10/10/2019	$0.00
EE3 Fab and Delivery	10/11/2019	12/9/2019	$0.00
Receive EE3	12/10/2019	12/10/2019	$26,000.00
Issue Req	9/25/2019	9/26/2019	$640.00
Issue PO	9/27/2019	10/10/2019	$0.00
EE4 Fab and Delivery	10/11/2019	12/9/2019	$0.00
Receive EE4	12/10/2019	12/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.

Re: Spreading Budgeted Cost Across Months

Stachu — Mon, 15 Jul 2019 13:39:36 GMT

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?

Re: Spreading Budgeted Cost Across Months

juliecornelius — Mon, 15 Jul 2019 13:47:38 GMT

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.

Re: Spreading Budgeted Cost Across Months

Stachu — Thu, 18 Jul 2019 04:29:49 GMT

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!

Re: Spreading Budgeted Cost Across Months

juliecornelius — Thu, 18 Jul 2019 17:00:28 GMT

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

Ideas?

Re: Spreading Budgeted Cost Across Months

Anonymous — Thu, 18 Jul 2019 20:29:24 GMT

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

Re: Spreading Budgeted Cost Across Months

Anonymous — Thu, 18 Jul 2019 21:26:54 GMT

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

Re: Spreading Budgeted Cost Across Months

Stachu — Fri, 19 Jul 2019 11:21:43 GMT

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

Re: Spreading Budgeted Cost Across Months

juliecornelius — Mon, 22 Jul 2019 20:32:00 GMT

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

Re: Spreading Budgeted Cost Across Months

juliecornelius — Mon, 22 Jul 2019 20:35:28 GMT

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.

Re: Spreading Budgeted Cost Across Months

Anonymous — Tue, 23 Jul 2019 16:59:40 GMT

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

Best

Darek

Re: Spreading Budgeted Cost Across Months

juliecornelius — Tue, 23 Jul 2019 18:36:24 GMT

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.

Re: Spreading Budgeted Cost Across Months

Anonymous — Wed, 24 Jul 2019 10:13:49 GMT

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

Re: Spreading Budgeted Cost Across Months

juliecornelius — Wed, 24 Jul 2019 13:51:41 GMT

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

Re: Spreading Budgeted Cost Across Months

juliecornelius — Wed, 24 Jul 2019 18:26:54 GMT

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?

Re: Spreading Budgeted Cost Across Months

Stachu — Fri, 26 Jul 2019 15:54:19 GMT

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

<=EOMONTH(Tasks[TaskFinishDate], 0)

Re: Spreading Budgeted Cost Across Months

Anonymous — Fri, 26 Jul 2019 17:03:36 GMT

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

Re: Spreading Budgeted Cost Across Months

Anonymous — Mon, 29 Jul 2019 21:07:01 GMT

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