Solved: Creating a Dashboard for operations

kaytaylor19 · ‎12-04-2025

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 · ‎12-04-2025

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.

View solution in original post

kaytaylor19 · ‎12-19-2025

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])))

Praful_Potphode · ‎12-05-2025

Hi @kaytaylor19

Try sample PBIX and let me know.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

Kedar_Pande · ‎12-05-2025

@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

GeraldGEmerick · ‎12-04-2025

@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 · ‎12-04-2025

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

GeraldGEmerick · ‎12-04-2025

@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

Anonymous · ‎12-04-2025

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.

Creating a Dashboard for operations

1. Calculate Averages for the Last 90 Days

Populate Predictions

Helpful resources

Power BI Dataviz World Championships

Power BI Monthly Update - January 2026

FabCon Atlanta 2026

FabCon is coming to Atlanta

Creating a Dashboard for operations

1. Calculate Averages for the Last 90 Days

Populate Predictions

Helpful resources

Power BI Dataviz World Championships

Power BI Monthly Update - January 2026

FabCon Atlanta 2026