Calculating Cost per Day Over Time for Project

[say_1839]

Hi all,

I am trying to calculate the cost per day for several ongoing projects. Each project has several phases with a start and end date, in a table called Timeline. 

Timeline

Project	Phase	Start-Phase	End-Phase
Project1	Phase1	1/1/2020	3/16/2020
Project1	Phase2	3/17/2020	6/15/2020
Project1	Phase3	6/16/2020	10/14/2020
Project1	Phase4	10/15/2020	9/15/2022
Project2	Phase1	4/14/2022	6/28/2022
Project2	Phase2	6/29/2022	9/27/2022
Project2	Phase3	9/28/2022	1/26/2023
Project2	Phase4	1/27/2023	12/27/2024
Project3	Phase1	9/22/2021	12/6/2021
Project3	Phase2	12/7/2021	3/7/2022
Project3	Phase3	3/8/2022	7/6/2022
Project3	Phase4	7/7/2022	6/6/2024
Project4	Phase1	7/4/2022	9/17/2022
Project4	Phase2	9/18/2022	12/17/2022
Project4	Phase3	12/18/2022	4/17/2023
Project4	Phase4	4/18/2023	3/18/2025
Project5	Phase1	12/16/2021	3/1/2022
Project5	Phase2	3/2/2022	5/31/2022
Project5	Phase3	6/1/2022	9/29/2022
Project5	Phase4	9/30/2022	8/30/2024

Each Phase has a standard cost assigned to it. For example, the cost for Phase 1 in Project 1 would be the same as the cost for Phase 1 in Project 2. This data is in a table called Phase Cost.

Phase Cost

Phase	Cost
Phase1	40
Phase2	70
Phase3	200
Phase4	500

First, I added to calculated columns to the Timeline table. The first one is just the duration of the phase using DATEDIFF, and the second is a "key", which is just the project and phase name concatenated.

I included a Calendar table in the model for the calculations.

Then I wanted to see how many projects were in Phase 1 on any given day. I put together the code below based on different blog posts, and it works. I can create graphs with time on the x-axis and see by the day how many phases are in Phase 1.

Active Phase 1 = 
CALCULATE (
    DISTINCTCOUNT ( Timeline[Key] ),
    GENERATE (
        VALUES ( 'Calendar'[Date] ),
        FILTER (
            CALCULATETABLE('Timeline', ALL('Calendar')),
            CONTAINS (
                DATESBETWEEN (
                    'Calendar'[Date],
                    'Timeline'[Start-Phase],
                    'Timeline'[End-Phase]
                ),
                'Calendar'[Date], 'Calendar'[Date]
            )&&(Timeline[Phase]="Phase1")
        )
    ),
    CROSSFILTER('Calendar'[Date], Timeline[Start-Phase], None) // had to be added to disable the active relationship
)

Now, I want to calculate how much money is spent on each day. Based on the Phase Cost table, Phase 1 would cost 40 dollars. Phase 1 of Project 1 is 75 days long, so the cost is about 0.53/day. How could I write a measure so that I can graph this cost over time like I am able to graph active Phase 1 over time? Note that the length of Phase 1 for each project can be different, but in this sample data set each Phase for each project is the same.

I would really appreciate any advice! I've been fighting with this for a while.

Thank you

[Anonymous]

Hi there.

This model not the right one to carry out the calculations you want in an easy and fast manner. Each and every project (in each phase) should have one entry in a fact table for each and every day that it lasts for. So, no start and end date needed. The daily cost for each project-phase whould then be assigned to each of these days in the fact table. Calendar would then be connected to the days via [Date]. With such a model EVERYTHING becomes dead easy. If you want to know the number of projects in phase one on a certain day (or in any period of time), you just write this measure:

[# Projects Active] = DISTINCTCOUNT( FactTable[ProjectID] )

and slice by the attributes you're interested in.

Wanna total cost over a period of time? Easy:

 [Total Cost] = SUM( FactTable[DailyCost] )

Please change the model and make your life (MUCH) easier.

Best

D

[say_1839]

Hi @Anonymous ,

Thank you for your reply! I understand that my data is not set up to make these calculations easy, but this is how it is formatted coming from the source file. Do you have a suggestion on how I can transform my data as you have described? I've tried CROSSJOIN to make a new table, but I can't get the formula correct. Any advice you have would be helpful. Thank you!

[Anonymous · Accepted solution]

// You can create the fact table in DAX
// but this is suboptimal since the original
// table will be present in the model taking
// space unnecessarily. This should be done
// in Power Query. The original table, of course,
// should be hidden and not used for anything.


[FactTable] =
GENERATE(

	SELECTCOLUMNS(
		OriginalTable,
		"Project", OriginalTable[Project],
		"Phase", OriginalTable[Phase],
		"Start", OriginalTable[Start-Phase],
		"End", OriginalTable[End-Phase],
		"Duration", OriginalTable[Duration]
	),
	
	// This is the field to join to Calendar.
	// The other date fields are qualitative
	// attributes only. All fields on which
	// you join to dimensions should always be
	// hidden.
	var __startDate = [Start]
	var __endDate = [End]
	var __phase = [Phase]
	var __duration = [Duration]
	var __cost =
		MAXX(
			filter(
				Costs, // stores costs for phases
				Costs[Phase] = __phase
			),
			Costs[Cost] / __duration
		)
	return
		SELECTCOLUMNS(
			CROSSJOIN(
				CALENDAR( __startDate, __endDate ),
				{ __cost }
			),
			"Date", [Date],
			"DailyCost", [Value]
		)
)

Best

D

[say_1839]

That worked perfectly! Thank you for your help @Anonymous !!

[Anonymous]

Why don't you use Power Query to massage the data?

Best
D

[say_1839]

I just checked my post, and I didn't paste the tables in clearly. Please see below. Sorry about that!