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,
I have a table of work orders. Each row has an unique ID, a start date, an end date, and a daily bill rate. With this, I need to calculate the projected spend into the future based on the different start and end dates for each row of data. Note that date data is kept in a separate dimDate table.
Say I have a table called dimDate (note dates are in DD/MM/YYYY format):
Date | Year | MonthName | MonthNum |
1/01/2015 | 2015 | Jan | 201501 |
2/01/2015 | 2015 | Jan | 201501 |
3/01/2015 | 2015 | Jan | 201501 |
4/01/2015 | 2015 | Jan | 201501 |
… | |||
31/12/2025 | 2025 | Dec | 202512 |
And I have a Workorder table (again, dates are in DD/MM/YYYY format):
WorkOrderID | Workorder Start Date | Workorder End Date | Daily Bill Rate |
WO000004 | 1/02/2016 | 30/12/2017 | 85.75 |
WO000001 | 2/01/2018 | 30/06/2018 | 50.25 |
WO000005 | 14/05/2020 | 6/07/2022 | 125.95 |
WO000007 | 22/02/2022 | 3/11/2025 | 160.55 |
WO000006 | 18/03/2022 | 25/08/2024 | 145.25 |
WO000002 | 15/09/2022 | 30/05/2025 | 90.85 |
WO000008 | 1/01/2023 | 30/04/2024 | 87.9 |
WO000003 | 25/04/2023 | 18/10/2024 | 140.5 |
Neither of the above two tables has any relationship setup.
How might I get the following expected result:
Date | WorkOrderID | Projected Spend |
1/01/2015 | blank | 0 |
2/01/2015 | blank | 0 |
3/01/2015 | blank | 0 |
… | ||
1/02/2016 | WO000004 | 85.75 |
… | ||
1/03/2022 | WO000005 | 125.95 |
1/03/2022 | WO000007 | 160.55 |
… | ||
19/03/2022 | WO000005 | 125.95 |
19/03/2022 | WO000007 | 160.55 |
19/03/2022 | WO000006 | 145.25 |
… | ||
30/09/2024 | WO000002 | 90.85 |
30/09/2024 | WO000003 | 140.5 |
The "..." denote a continuation of the date values. The expected result should also be able to calculate projected spend into the future dates from today as can be seen in the lat two rows.
How might I go about implementing this in Power BI?
Thanks in advance.
Solved! Go to Solution.
Try this calculated table. It returns a row for each day within the start/end dates for each WorkOrderID. Instead of adding blank fact table rows for every day in your DimDate table (bloats your fact table), you could create a relationship between DimDate and ProjectedSpend. Then, in visuals use DimDate and specify "Show items with no data" if you want to see every row in DimDate.
ProjectedSpend =
VAR vProjectedSpend =
GENERATE (
Workorder,
VAR vStartDate = Workorder[Workorder Start Date]
VAR vEndDate = Workorder[Workorder End Date]
VAR vCalendar =
CALENDAR ( vStartDate, vEndDate )
RETURN
vCalendar
)
VAR vResult =
SELECTCOLUMNS (
vProjectedSpend,
"Date", [Date],
"WorkOrderID", Workorder[WorkOrderID],
"Projected Spend", Workorder[Daily Bill Rate]
)
RETURN
vResult
Proud to be a Super User!
Try this calculated table. It returns a row for each day within the start/end dates for each WorkOrderID. Instead of adding blank fact table rows for every day in your DimDate table (bloats your fact table), you could create a relationship between DimDate and ProjectedSpend. Then, in visuals use DimDate and specify "Show items with no data" if you want to see every row in DimDate.
ProjectedSpend =
VAR vProjectedSpend =
GENERATE (
Workorder,
VAR vStartDate = Workorder[Workorder Start Date]
VAR vEndDate = Workorder[Workorder End Date]
VAR vCalendar =
CALENDAR ( vStartDate, vEndDate )
RETURN
vCalendar
)
VAR vResult =
SELECTCOLUMNS (
vProjectedSpend,
"Date", [Date],
"WorkOrderID", Workorder[WorkOrderID],
"Projected Spend", Workorder[Daily Bill Rate]
)
RETURN
vResult
Proud to be a Super User!
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 |
---|---|
99 | |
99 | |
80 | |
77 | |
66 |
User | Count |
---|---|
134 | |
106 | |
105 | |
86 | |
72 |