Forum Discussion

kaytaylor19's avatar
kaytaylor19
New Member
8 months ago
Solved

Creating a Dashboard for operations

I am creating a dashboard that will show current, predicted and planned for our operations team. I am stuck with how to do the predicted (DAX). The predicted numbers are based on the avg of the last ...
  • Anonymous's avatar
    Anonymous
    8 months ago

    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.

GeraldGEmerick's avatar
GeraldGEmerick
Super User
8 months ago

kaytaylor19 Providing some sample data here would be extremely useful. Your calculation would probably look something like the following:

90 Day Average Per Site and Weekday =
VAR _Date = MAX( 'Table'[Date] )
VAR _DateMinus90 = _Date - 90
VAR _Weekday = WEEKDAY( _Date )
VAR _Return = CALCULATE( AVERAGE( 'Table'[Value] ), 'Table'[Date] <= _Date, 'Table'[Date] >= _DateMinus90, WEEKDAY( 'Table'[Date] ) = _Weekday )
RETURN _Return
kaytaylor19's avatar
kaytaylor19
New Member
8 months ago

Please see sample data below

MonthDateDay #Puerto Rico Hours Puerto Rico Units 
Oct10/01/20254254.47                        29,238
Oct10/02/20255259.27                        30,779
Oct10/03/20256267.10                        18,209
Oct10/04/2025743.05                                 -  
Oct10/05/2025152.12                        27,082
Oct10/06/20252241.60                        28,744
Oct10/07/20253250.18                        29,723
Oct10/08/20254274.67                        29,673
Oct10/09/20255284.07                        34,362
Oct10/10/20256264.48                        16,631
Oct10/11/2025740.87                                 -  
Oct10/12/2025163.20                        26,181
Oct10/13/20252251.77                        25,803
Oct10/14/20253269.53                        29,369
Oct10/15/20254280.25                        28,857
Oct10/16/20255267.20                        29,274
Oct10/17/20256255.55                        17,490
Oct10/18/2025741.35                                 -  
Oct10/19/2025154.33                        26,362
Oct10/20/20252247.98                        26,759
Oct10/21/20253245.75                        29,939
Oct10/22/20254251.37                        26,620
Oct10/23/20255242.70                        31,446
Oct10/24/20256223.47                        17,730
Oct10/25/2025739.60                                 -  
Oct10/26/2025149.63                        26,649
Oct10/27/20252254.83                        29,112
Oct10/28/20253261.42                        31,961
Oct10/29/20254259.52                        30,798
Oct10/30/20255258.70                        35,307
Oct10/31/20256262.72                        16,416
Nov11/01/2025741.17                                 -  
Nov11/02/2025163.12                        28,135
Nov11/03/20252285.22                        29,319
Nov11/04/20253248.73                        32,098
Nov11/05/20254290.23                        30,365
Nov11/06/20255252.60                        37,437
Nov11/07/20256245.55                        16,697
Nov11/08/2025747.52                                 -  
Nov11/09/2025167.73                        28,656
Nov11/10/20252228.62                        30,291
Nov11/11/20253284.88                        31,177
Nov11/12/20254252.75                        28,315
Nov11/13/202550.00                                 -  
Nov11/14/20256272.59                        16,845
Nov11/15/2025742.11                                  1
Nov11/16/2025160.77                        27,265
Nov11/17/20252259.19                        29,356
Nov11/18/20253281.58                        30,058
Nov11/19/20254280.61                        29,109
Nov11/20/20255276.53                        33,072
Nov11/21/20256272.59                        16,845
Nov11/22/2025742.11                                  1
Nov11/23/2025160.77                        27,265
Nov11/24/20252259.19                        29,356
Nov11/25/20253281.58                        30,058
Nov11/26/20254280.61                        29,109
Nov11/27/20255276.53                        33,072
Nov11/28/20256272.59                        16,845
Nov11/29/2025742.11                                  1
Nov11/30/2025160.77                        27,265
Dec12/01/20252259.19                        29,356
Dec12/02/20253281.58                        30,058
Dec12/03/20254280.61                        29,109
Dec12/04/20255276.53                        33,072
Dec12/05/20256272.59                        16,845
Dec12/06/2025742.11                                  1
Dec12/07/2025160.77                        27,265
Dec12/08/20252259.19                        29,356
Dec12/09/20253281.58                        30,058
Dec12/10/20254280.61                        29,109
Dec12/11/20255276.53                        33,072
Dec12/12/20256272.59                        16,845
Dec12/13/2025742.11                                  1
Dec12/14/2025160.77                        27,265
Dec12/15/20252259.19                        29,356
Dec12/16/20253281.58                        30,058
Dec12/17/20254280.61                        29,109
Dec12/18/20255276.53                        33,072
Dec12/19/20256272.59                        16,845
Dec12/20/2025742.11                                  1
Dec12/21/2025160.77                        27,265
Dec12/22/20252259.19                        29,356
Dec12/23/20253281.58                        30,058
Dec12/24/20254280.61                        29,109
Dec12/25/20255276.53                        33,072
Dec12/26/20256272.59                        16,845
Dec12/27/2025742.11                                  1
Dec12/28/2025160.77                        27,265
Dec12/29/20252259.19                        29,356
Dec12/30/20253281.58                        30,058
Dec12/31/20254280.61                        29,109
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 months ago

    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.

  • GeraldGEmerick's avatar
    GeraldGEmerick
    Super User
    8 months ago

    kaytaylor19 That helps quite a bit. Here is the revised formula:

    90 Day Average Per Site and Weekday = 
VAR _Value = MAX( 'Table'[Puerto Rico Hours] )
VAR _Date = IF( _Value = BLANK(), DATE( 2025, 12, 31 ), MAX( 'Table'[Date] ) )
VAR _DateMinus90 = _Date - 90
VAR _Weekday = WEEKDAY( MAX( 'Dates'[Date] ) )
VAR _Return = CALCULATE( AVERAGE( 'Table'[Puerto Rico Hours] ), ALL( 'Table' ), 'Table'[Date] <= _Date, 'Table'[Date] >= _DateMinus90, WEEKDAY( 'Table'[Date] ) = _Weekday )
RETURN _Return