Forum Discussion
DMB90
Helper II
Helper IILooking for Specific Formula Assistance
Hello,
I want to create a formula to basically state:
Calculate the sum of Actual Design hours (column already created) for a specified date range (3/1/2025 to 5/31/2025) and for the corresponding Process if the Activity Type is "Control", and if the sum from that range is greater than the Average of Budgeted Design hours (column already created), then say "Over Budget", if not, then "Within Budget"
DMB90 , Try below, assuming you have date table
Budget Status = VAR _ActualHours= CALCULATE(SUM('FactTable'[Actual Design Hours]),'FactTable'[Activity Type] = "Control",DATESBETWEEN( 'Calendar'[Date],DATE(2025,3,1), DATE(2025,5,31) )) VAR _AvgBudget = CALCULATE( AVERAGE('FactTable'[Budgeted Design Hours]), 'FactTable'[Activity Type] = "Control") RETURN IF(_ActualHours > _AvgBudget,"Over Budget","Within Budget" )
1 Reply
- amitchandak
Super User
DMB90 , Try below, assuming you have date table
Budget Status = VAR _ActualHours= CALCULATE(SUM('FactTable'[Actual Design Hours]),'FactTable'[Activity Type] = "Control",DATESBETWEEN( 'Calendar'[Date],DATE(2025,3,1), DATE(2025,5,31) )) VAR _AvgBudget = CALCULATE( AVERAGE('FactTable'[Budgeted Design Hours]), 'FactTable'[Activity Type] = "Control") RETURN IF(_ActualHours > _AvgBudget,"Over Budget","Within Budget" )