I am trying to do some Calculations to figure out "Wage" based on certain Criteria. Here is what I have:
SUMX(
'Federal Holidays',
SWITCH(
TRUE(),
'Federal Holidays'[Holiday] = "New Year's Day 3 Days After" || 'Federal Holidays'[Holiday] = "New Year's Day 3 Days Before",
IFERROR(
CALCULATE(
Round( [_hours],0) * [_estimatedWageAverage]
,'PTO/VTO/OTAppendNICEtoCalabrio'[_exception] IN {"VTO" , "Voluntary Time Off (VTO)"}
,Dates[month index] > -16
,Dates[month index] < -1
),0
)
,'Federal Holidays'[Holiday] <> "New Year's Day 3 Days After" || 'Federal Holidays'[Holiday] <> "New Year's Day 3 Days Before",
IFERROR(
CALCULATE(
Round( [_hours],0) * [_estimatedWageAverage]
,'PTO/VTO/OTAppendNICEtoCalabrio'[_exception] IN {"VTO" , "Voluntary Time Off (VTO)"}
,Dates[year index] = 0
),0
)
)
)
What I need to remove is the SUMX part. I cannot figout another way to do this calculation.
What I am looking to do in one Formula, is to do something like this(I did Merge my Federal Holiday table to my Main Table):
_estimatedSavingsCurrentYear2 =
IF('PTO/VTO/OTAppendNICEtoCalabrio'[Holiday Name] = "New Year's Day 3 Days After" || 'PTO/VTO/OTAppendNICEtoCalabrio'[Holiday Name] = "New Year's Day 3 Days Before",
IFERROR(
CALCULATE(
Round( [_hours],0) * [_estimatedWageAverage]
,'PTO/VTO/OTAppendNICEtoCalabrio'[_exception] IN {"VTO" , "Voluntary Time Off (VTO)"}
,Dates[month index] > -16
,Dates[month index] < -1
),0
),
IFERROR(
CALCULATE(
Round( [_hours],0) * [_estimatedWageAverage]
,'PTO/VTO/OTAppendNICEtoCalabrio'[_exception] IN {"VTO" , "Voluntary Time Off (VTO)"}
,Dates[year index] = 0
),0
)
)
'PTO/VTO/OTAppendNICEtoCalabrio'[Holiday Name] is a Column within the table.
