Creating a Dashboard for operations

Hi kaytaylor19 ,

Follow below steps.

1. Create a separate Dates table

Dates =

ADDCOLUMNS (

    CALENDAR ( DATE(2025,1,1), DATE(2026,12,31) ),

    "Weekday", WEEKDAY ( [Date], 1 ) -- Sunday = 1 ... Saturday = 7

)

Create a relationship:

Dates[Date] → YourData[Date]

2. 90-Day Average by Weekday (Hours)

Avg Hours Last 90 Days =

VAR _Today = MAX ( Dates[Date] )

VAR _MinDate = _Today - 90

VAR _Weekday = MAX ( Dates[Weekday] )

RETURN

CALCULATE (

    AVERAGE ( 'YourData'[Puerto Rico Hours] ),

    'YourData'[Date] >= _MinDate,

    'YourData'[Date] < _Today,

    'YourData'[Day #] = _Weekday -- weekday match

)

3. 90-Day Average by Weekday (Units)

Avg Units Last 90 Days =

VAR _Today = MAX ( Dates[Date] )

VAR _MinDate = _Today - 90

VAR _Weekday = MAX ( Dates[Weekday] )

RETURN

CALCULATE (

    AVERAGE ( 'YourData'[Puerto Rico Units] ),

    'YourData'[Date] >= _MinDate,

    'YourData'[Date] < _Today,

    'YourData'[Day #] = _

Weekday

)

4. You want:

If the date exists in your actual table → use actuals

If the date has NO data (future) → use the 90-day weekday average

Predicted Hours Measure

Predicted Hours =

VAR _ActualHours =

    CALCULATE (

        SUM ( 'YourData'[Puerto Rico Hours] ),

        REMOVEFILTERS ( Dates ) -- keep row context

    )

RETURN

IF (

    NOT ISBLANK ( _ActualHours ),

    _ActualHours, -- actual exists

    [Avg Hours Last 90 Days] -- otherwise predicted

)

Predicted Units Measure

Predicted Units =

VAR _ActualUnits =

    CALCULATE (

        SUM ( 'YourData'[Puerto Rico Units] ),

        REMOVEFILTERS ( Dates )

    )

RETURN

IF (

    NOT ISBLANK ( _ActualUnits ),

    _ActualUnits,

    [Avg Units Last 90 Days]

)

If my response as resolved your issue please mark it as solution and give kudos.