Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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!
// 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
That worked perfectly! Thank you for your help @Anonymous !!
I just checked my post, and I didn't paste the tables in clearly. Please see below. Sorry about that!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |