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