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 90 days for specific day numbers. can someone help? I need to:1. Calculate Averages for the Last 90 DaysFilter the dataset to the previous 90 days.For each weekday and site, calculate:Average Hours = (Sum of hours for that weekday over 90 days) ÷ (Number of occurrences)Average Units = (Sum of units for that weekday over 90 days) ÷ (Number of occurrences)Optionally calculate UPH (Units per Hour) = Average Units ÷ Average Hours Build Prediction LogicCreate a calendar of future dates (e.g., for the month or year).For each future date:Identify its weekday.If actual data exists for that date (e.g., past days in the current month), use actual values.If no actual data exists (future days), use the average for that weekday from Step 1.Populate PredictionsFor each site and date:Predicted Hours = Average Hours for that weekday.Predicted Units = Average Units for that weekday.Sum these up for monthly totals or yearly totals.

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 - 90VAR _Weekday = MAX ( Dates[Weekday] )RETURNCALCULATE ( 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 - 90VAR _Weekday = MAX ( Dates[Weekday] )RETURNCALCULATE ( 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 )RETURNIF ( 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 ) )RETURNIF ( NOT ISBLANK ( _ActualUnits ), _ActualUnits, [Avg Units Last 90 Days]) If my response as resolved your issue please mark it as solution and give kudos.

Forum Discussion

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 90 days for specific day numbers. can someone help?

I need to:

1. Calculate Averages for the Last 90 Days

Filter the dataset to the previous 90 days.
For each weekday and site, calculate:
- Average Hours = (Sum of hours for that weekday over 90 days) ÷ (Number of occurrences)
- Average Units = (Sum of units for that weekday over 90 days) ÷ (Number of occurrences)
Optionally calculate UPH (Units per Hour) = Average Units ÷ Average Hours
Build Prediction Logic
- Create a calendar of future dates (e.g., for the month or year).
- For each future date:
  - Identify its weekday.
  - If actual data exists for that date (e.g., past days in the current month), use actual values.
  - If no actual data exists (future days), use the average for that weekday from Step 1.
Populate Predictions
- For each site and date:
  - Predicted Hours = Average Hours for that weekday.
  - Predicted Units = Average Units for that weekday.
- Sum these up for monthly totals or yearly totals.

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.

7 Replies

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

New Member

8 months ago

Please see sample data below

Month	Date	Day #	Puerto Rico Hours	Puerto Rico Units
Oct	10/01/2025	4	254.47	29,238
Oct	10/02/2025	5	259.27	30,779
Oct	10/03/2025	6	267.10	18,209
Oct	10/04/2025	7	43.05	-
Oct	10/05/2025	1	52.12	27,082
Oct	10/06/2025	2	241.60	28,744
Oct	10/07/2025	3	250.18	29,723
Oct	10/08/2025	4	274.67	29,673
Oct	10/09/2025	5	284.07	34,362
Oct	10/10/2025	6	264.48	16,631
Oct	10/11/2025	7	40.87	-
Oct	10/12/2025	1	63.20	26,181
Oct	10/13/2025	2	251.77	25,803
Oct	10/14/2025	3	269.53	29,369
Oct	10/15/2025	4	280.25	28,857
Oct	10/16/2025	5	267.20	29,274
Oct	10/17/2025	6	255.55	17,490
Oct	10/18/2025	7	41.35	-
Oct	10/19/2025	1	54.33	26,362
Oct	10/20/2025	2	247.98	26,759
Oct	10/21/2025	3	245.75	29,939
Oct	10/22/2025	4	251.37	26,620
Oct	10/23/2025	5	242.70	31,446
Oct	10/24/2025	6	223.47	17,730
Oct	10/25/2025	7	39.60	-
Oct	10/26/2025	1	49.63	26,649
Oct	10/27/2025	2	254.83	29,112
Oct	10/28/2025	3	261.42	31,961
Oct	10/29/2025	4	259.52	30,798
Oct	10/30/2025	5	258.70	35,307
Oct	10/31/2025	6	262.72	16,416
Nov	11/01/2025	7	41.17	-
Nov	11/02/2025	1	63.12	28,135
Nov	11/03/2025	2	285.22	29,319
Nov	11/04/2025	3	248.73	32,098
Nov	11/05/2025	4	290.23	30,365
Nov	11/06/2025	5	252.60	37,437
Nov	11/07/2025	6	245.55	16,697
Nov	11/08/2025	7	47.52	-
Nov	11/09/2025	1	67.73	28,656
Nov	11/10/2025	2	228.62	30,291
Nov	11/11/2025	3	284.88	31,177
Nov	11/12/2025	4	252.75	28,315
Nov	11/13/2025	5	0.00	-
Nov	11/14/2025	6	272.59	16,845
Nov	11/15/2025	7	42.11	1
Nov	11/16/2025	1	60.77	27,265
Nov	11/17/2025	2	259.19	29,356
Nov	11/18/2025	3	281.58	30,058
Nov	11/19/2025	4	280.61	29,109
Nov	11/20/2025	5	276.53	33,072
Nov	11/21/2025	6	272.59	16,845
Nov	11/22/2025	7	42.11	1
Nov	11/23/2025	1	60.77	27,265
Nov	11/24/2025	2	259.19	29,356
Nov	11/25/2025	3	281.58	30,058
Nov	11/26/2025	4	280.61	29,109
Nov	11/27/2025	5	276.53	33,072
Nov	11/28/2025	6	272.59	16,845
Nov	11/29/2025	7	42.11	1
Nov	11/30/2025	1	60.77	27,265
Dec	12/01/2025	2	259.19	29,356
Dec	12/02/2025	3	281.58	30,058
Dec	12/03/2025	4	280.61	29,109
Dec	12/04/2025	5	276.53	33,072
Dec	12/05/2025	6	272.59	16,845
Dec	12/06/2025	7	42.11	1
Dec	12/07/2025	1	60.77	27,265
Dec	12/08/2025	2	259.19	29,356
Dec	12/09/2025	3	281.58	30,058
Dec	12/10/2025	4	280.61	29,109
Dec	12/11/2025	5	276.53	33,072
Dec	12/12/2025	6	272.59	16,845
Dec	12/13/2025	7	42.11	1
Dec	12/14/2025	1	60.77	27,265
Dec	12/15/2025	2	259.19	29,356
Dec	12/16/2025	3	281.58	30,058
Dec	12/17/2025	4	280.61	29,109
Dec	12/18/2025	5	276.53	33,072
Dec	12/19/2025	6	272.59	16,845
Dec	12/20/2025	7	42.11	1
Dec	12/21/2025	1	60.77	27,265
Dec	12/22/2025	2	259.19	29,356
Dec	12/23/2025	3	281.58	30,058
Dec	12/24/2025	4	280.61	29,109
Dec	12/25/2025	5	276.53	33,072
Dec	12/26/2025	6	272.59	16,845
Dec	12/27/2025	7	42.11	1
Dec	12/28/2025	1	60.77	27,265
Dec	12/29/2025	2	259.19	29,356
Dec	12/30/2025	3	281.58	30,058
Dec	12/31/2025	4	280.61	29,109

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.

Kedar_Pande
Super User
8 months ago
kaytaylor19

Predicted Hours =
VAR CurrentDate = MAX('Calendar'[Date])
VAR Days90Ago = CurrentDate - 90
VAR WeekdayNum = WEEKDAY(CurrentDate, 2)
VAR AvgHours =
CALCULATE(
AVERAGEX(
FILTER(
'Operations',
'Operations'[Date] >= Days90Ago &&
'Operations'[Date] <= CurrentDate &&
WEEKDAY('Operations'[Date], 2) = WeekdayNum &&
NOT ISBLANK('Operations'[Hours])
),
'Operations'[Hours]
),
ALLEXCEPT('Operations', 'Operations'[Site])
)
RETURN
IF(CurrentDate <= MAX('Operations'[Date]),
SUM('Operations'[Hours]),
AvgHours
)

Predicted Units =
-- Same structure, replace [Hours] with [Units]

Uses actual data when available, predicts future with 90-day weekday average per site. Updates dynamically.

If this answer helped, please click Kudos or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande
Praful_Potphode
Super User
8 months ago
Hi kaytaylor19
Try sample PBIX and let me know.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,
Praful
kaytaylor19
New Member
8 months ago
I have the DAX below but feel its too time consuming to do this for 27 different departments (003 is a DC). I have a table named DimDPS that has all department information. I used the yearly pred date as today not sure if that is right either but if it helps, our FY starts 10/1. I have to do this for 3 KPI's (Hours, Units and UPH which is units over Total hours). Any help would be greatly appreciated.

PRHOURSS = VAR _Today = MAX ( 'USSCKPI KPIProductivity'[Date])
VAR _Last90 = _Today - 90
VAR _Avg=
AVERAGEX(FILTER(ALL(
'90 DAY AVG USSCKPI KPIProd'),'90 DAY AVG USSCKPI KPIProd'[DC]="003" && '90 DAY AVG USSCKPI KPIProd'[Date]<= _Today && '90 DAY AVG USSCKPI KPIProd'[Date]>= _Last90 && YearlyPred[WEEKDAY]='90 DAY AVG USSCKPI KPIProd'[Weekday]),'90 DAY AVG USSCKPI KPIProd'[TOTAL HOURS FOR AVG])
RETURN
IF(YearlyPred[Date] > DATE(2025,12,19), _Avg,CALCULATE(SUM('USSCKPI KPIProductivity'[Total Hours for AVG]),'USSCKPI KPIProductivity'[DC]="003",'USSCKPI KPIProductivity'[Date]=EARLIER(YearlyPred[Date])))

Forum Discussion

Creating a Dashboard for operations

1. Calculate Averages for the Last 90 Days

Populate Predictions

7 Replies

Related content

Preparing a operational dashboard

SQL Operator with multiple destinations

How to create a beautiful Power BI Dashboard/Report?

Create an Organisation chart Dashboard on PowerBI with interactivity

Use Wildcard Operator