Calculating projected spend between two dates

[dwilli10]

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. 

[DataInsights · Accepted solution]

@dwilli10,

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