Essential Operations DAX Formulas
1. Production Output 
Total Output = SUM(Production[UnitsProduced])
Output MTD =
CALCULATE([Total Output], DATESMTD(DateTable[Date]))
Output YTD =
TOTALYTD([Total Output], DateTable[Date])
Output LY =
CALCULATE([Total Output], SAMEPERIODLASTYEAR(DateTable[Date]))
Tracks production volume month-to-date, year-to-date, and compares with last year.
2. Cycle Time
Average Cycle Time =
AVERAGEX(Production, DATEDIFF(Production[StartDate], Production[EndDate], DAY))
Cycle Time YTD =
TOTALYTD([Average Cycle Time], DateTable[Date])
Measures average process completion time and trends.
3. Resource Utilization
Resource Utilization % =
DIVIDE(SUM(Resource[HoursUsed]), SUM(Resource[HoursAvailable]), 0)
Resource Utilization YTD =
TOTALYTD([Resource Utilization %], DateTable[Date])
Shows how effectively workforce/equipment is used.
4. Inventory Management
Inventory Levels = SUM(Inventory[Stock])
Inventory Turnover =
DIVIDE(SUM(Sales[COGS]), AVERAGE(Inventory[Stock]), 0)
Inventory Turnover YTD =
TOTALYTD([Inventory Turnover], DateTable[Date])
Tracks stock levels and turnover efficiency.
5. Supplier Performance
On-Time Deliveries % =
DIVIDE(
COUNTROWS(FILTER(Supplier, Supplier[DeliveryStatus] = "On-Time")),
COUNTROWS(Supplier),
0
)
On-Time Deliveries YTD =
TOTALYTD([On-Time Deliveries %], DateTable[Date])
Evaluates supplier reliability over time.
6. Operational Costs
Total Operational Cost = SUM(Operations[Cost])
Operational Cost YTD =
TOTALYTD([Total Operational Cost], DateTable[Date])
Cost Variance = [Total Operational Cost] - [Budgeted Operational Cost]
Monitors costs and compares against budget.
7. Downtime Tracking
Downtime Hours = SUM(Maintenance[DowntimeHours])
Downtime YTD =
TOTALYTD([Downtime Hours], DateTable[Date])
Tracks equipment downtime and trends.
Operations DAX Formulas with Date Intelligence
1. Production Output
Total Output = SUM(Production[UnitsProduced])
Output MTD =
CALCULATE([Total Output], DATESMTD(DateTable[Date]))
Output YTD =
TOTALYTD([Total Output], DateTable[Date])
Output LY =
CALCULATE([Total Output], SAMEPERIODLASTYEAR(DateTable[Date]))
Output Growth % =
DIVIDE([Output YTD] - [Output LY], [Output LY], 0)
Total Output = SUM(Production[UnitsProduced])
Output MTD =
CALCULATE([Total Output], DATESMTD(DateTable[Date]))
Output YTD =
TOTALYTD([Total Output], DateTable[Date])
Output LY =
CALCULATE([Total Output], SAMEPERIODLASTYEAR(DateTable[Date]))
Output Growth % =
DIVIDE([Output YTD] - [Output LY], [Output LY], 0)
Tracks production volume month-to-date, year-to-date, and compares with last year.
2. Cycle Time
Average Cycle Time =
AVERAGEX(Production, DATEDIFF(Production[StartDate], Production[EndDate], DAY))
Cycle Time YTD =
TOTALYTD([Average Cycle Time], DateTable[Date])
Cycle Time LY =
CALCULATE([Average Cycle Time], SAMEPERIODLASTYEAR(DateTable[Date]))
Average Cycle Time =
AVERAGEX(Production, DATEDIFF(Production[StartDate], Production[EndDate], DAY))
Cycle Time YTD =
TOTALYTD([Average Cycle Time], DateTable[Date])
Cycle Time LY =
CALCULATE([Average Cycle Time], SAMEPERIODLASTYEAR(DateTable[Date]))
Measures average process completion time and trends over time.
3. Resource Utilization
Resource Utilization % =
DIVIDE(SUM(Resource[HoursUsed]), SUM(Resource[HoursAvailable]), 0)
Resource Utilization MTD =
CALCULATE([Resource Utilization %], DATESMTD(DateTable[Date]))
Resource Utilization YTD =
TOTALYTD([Resource Utilization %], DateTable[Date])
Resource Utilization % =
DIVIDE(SUM(Resource[HoursUsed]), SUM(Resource[HoursAvailable]), 0)
Resource Utilization MTD =
CALCULATE([Resource Utilization %], DATESMTD(DateTable[Date]))
Resource Utilization YTD =
TOTALYTD([Resource Utilization %], DateTable[Date])
Shows how effectively workforce/equipment is used.
4. Inventory Management
Inventory Levels = SUM(Inventory[Stock])
Inventory Turnover =
DIVIDE(SUM(Sales[COGS]), AVERAGE(Inventory[Stock]), 0)
Inventory Turnover YTD =
TOTALYTD([Inventory Turnover], DateTable[Date])
Inventory Turnover LY =
CALCULATE([Inventory Turnover], SAMEPERIODLASTYEAR(DateTable[Date]))
Inventory Levels = SUM(Inventory[Stock])
Inventory Turnover =
DIVIDE(SUM(Sales[COGS]), AVERAGE(Inventory[Stock]), 0)
Inventory Turnover YTD =
TOTALYTD([Inventory Turnover], DateTable[Date])
Inventory Turnover LY =
CALCULATE([Inventory Turnover], SAMEPERIODLASTYEAR(DateTable[Date]))
Tracks stock levels and turnover efficiency across periods.
5. Supplier Performance
On-Time Deliveries % =
DIVIDE(
COUNTROWS(FILTER(Supplier, Supplier[DeliveryStatus] = "On-Time")),
COUNTROWS(Supplier),
0
)
On-Time Deliveries YTD =
TOTALYTD([On-Time Deliveries %], DateTable[Date])
On-Time Deliveries LY =
CALCULATE([On-Time Deliveries %], SAMEPERIODLASTYEAR(DateTable[Date]))
On-Time Deliveries % =
DIVIDE(
COUNTROWS(FILTER(Supplier, Supplier[DeliveryStatus] = "On-Time")),
COUNTROWS(Supplier),
0
)
On-Time Deliveries YTD =
TOTALYTD([On-Time Deliveries %], DateTable[Date])
On-Time Deliveries LY =
CALCULATE([On-Time Deliveries %], SAMEPERIODLASTYEAR(DateTable[Date]))
Evaluates supplier reliability over time.
6. Operational Costs
Total Operational Cost = SUM(Operations[Cost])
Operational Cost MTD =
CALCULATE([Total Operational Cost], DATESMTD(DateTable[Date]))
Operational Cost YTD =
TOTALYTD([Total Operational Cost], DateTable[Date])
Operational Cost LY =
CALCULATE([Total Operational Cost], SAMEPERIODLASTYEAR(DateTable[Date]))
Cost Variance = [Total Operational Cost] - [Budgeted Operational Cost]
Total Operational Cost = SUM(Operations[Cost])
Operational Cost MTD =
CALCULATE([Total Operational Cost], DATESMTD(DateTable[Date]))
Operational Cost YTD =
TOTALYTD([Total Operational Cost], DateTable[Date])
Operational Cost LY =
CALCULATE([Total Operational Cost], SAMEPERIODLASTYEAR(DateTable[Date]))
Cost Variance = [Total Operational Cost] - [Budgeted Operational Cost]
Monitors costs monthly, yearly, and against budget.
7. Downtime Tracking
Downtime Hours = SUM(Maintenance[DowntimeHours])
Downtime MTD =
CALCULATE([Downtime Hours], DATESMTD(DateTable[Date]))
Downtime YTD =
TOTALYTD([Downtime Hours], DateTable[Date])
Downtime LY =
CALCULATE([Downtime Hours], SAMEPERIODLASTYEAR(DateTable[Date]))
Downtime Hours = SUM(Maintenance[DowntimeHours])
Downtime MTD =
CALCULATE([Downtime Hours], DATESMTD(DateTable[Date]))
Downtime YTD =
TOTALYTD([Downtime Hours], DateTable[Date])
Downtime LY =
CALCULATE([Downtime Hours], SAMEPERIODLASTYEAR(DateTable[Date]))
Tracks equipment downtime and compares across years.
Takeaway
These formulas give Operations teams time-based insights into production, efficiency, resource usage, supplier reliability, and costs. By layering date intelligence functions (DATESMTD, TOTALYTD, SAMEPERIODLASTYEAR), you can present monthly, yearly, and historical comparisons that drive continuous improvement.
Comments
Post a Comment