Showing average planned hours by day

[ArtNo1000]

Hi

I have a consolidated table like below and would like to build a simple forecast where I calculate average hours per day going forward. Any tips on how I can achive this?

Currently the "OrderDate" column is related to the date table, but its between StartDate and EndDate I need to do the forecast. 

Table
OrderID	OrderDate	StartDate	EndDate	PLanned time	Already logged time
1	2021-03-01	2021-03-22	2021-03-26	50	15

Result:
Calender[Date]	Planned time	Logged time	Remaining time
2021-03-22	10	10	0
2021-03-23	10	5	5
2021-03-24	10	0	10
2021-03-25	10	0	10
2021-03-26	10	0	10

[v-cazheng-msft · Accepted solution]

Hi

You can try the following steps.

1 Create a Calculated table

Result =

VAR cal =

    SELECTCOLUMNS (

        CALENDAR ( MIN ( 'Table'[StartDate] ), MAX ( 'Table'[EndDate] ) ),

        "date", [Date]

    )

VAR cross_t =

    FILTER (

        CROSSJOIN ( 'Table', cal ),

        [date] >= 'Table'[StartDate]

            && [date] <= 'Table'[EndDate]

    )

VAR sel_t =

    SELECTCOLUMNS (

        cross_t,

        "OrderID", [OrderID],

        "Calendar [Date]", [date],

        "Planned time", [PLanned time],

        "already", [Already logged time]

    )

RETURN

    sel_t

2 Create two Measures

Logged time =

VAR res =

    CALCULATE (

        SUM ( Result[Average planned time] ),

        FILTER (

            ALLEXCEPT ( Result, Result[OrderID] ),

            Result[Calendar_Date] <= MAX ( Result[Calendar_Date] )

        )

    )

VAR mid_res =

    res - MAX ( Result[already] )

RETURN

    IF (

        mid_res >= MAX ( Result[Average planned time] ),

        0,

        IF (

            mid_res > 0

                && mid_res < MAX ( Result[already] ),

            mid_res,

            MAX ( Result[Average planned time] )

        )

    )

Remaining time =

VAR res =

    CALCULATE (

        SUM ( Result[Average planned time] ),

        FILTER (

            ALLEXCEPT ( Result, Result[OrderID] ),

            Result[Calendar_Date] <= MAX ( Result[Calendar_Date] )

        )

    )

VAR mid_res =

    MAX ( Result[already] ) - res

RETURN

    IF ( mid_res < 0, 0, mid_res )

Create a table visual and the result looks like this:

For more details, you can refer the attached pbix.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.