approximate lookup dates
2 Topics
Trying to calculate a date based on working days from a date that is eneterd
Hi All, I am trying to find a way to calculate a date that is 22 working days past a date that is entered into a table. Need to make sure that weekends and federal holidays are not part of that 22 day count. I tried using NETWORKDAYS, but that option is not available in the environment I am working in. Example; Date entered 12/31/22 + 22 working days = 2/2/23. I need to calculate with a formula that will produce results for every day enteredSolved1.3KViews0likes5Comments
Linear Interpolation between dates
Hi I'm trying to do a measure called "Earned Schedule" which basically interpolates some value (EV) in a lookuptable and returns the Date we should have reached this value (and then displays it in Elapsed days) This should be the formula ES = VAR LowerPV = CALCULATE(MAX('EV Calculations'[PV]),FILTER('EV Calculations','EV Calculations'[PV]<[EV])) VAR HigherPV = CALCULATE(MIN('EV Calculations'[PV]),FILTER('EV Calculations','EV Calculations'[PV]>[EV])) VAR LowerDate = CALCULATE(MAX('EV Calculations'[Date]),FILTER('EV Calculations','EV Calculations'[PV]<[EV])) VAR HigherDate = CALCULATE(MIN('EV Calculations'[Date]),FILTER('EV Calculations','EV Calculations'[PV]>[EV])) RETURN LowerDate-[StartDate] + ([EV]-LowerPV)/(HigherPV-LowerPV)*(HigherDate-LowerDate) however though I do get the correct result. It interprets it as a date instead of a number. It displays 3/10/1900, 7:53:30 where it should be 70,32867. If I try to return a different part it usually shows correctly, unless I'm deducting Dates. Return StartDate 1/1/2020 12:00:00 AM Return HigherDate-LowerDate 1/30/1900 12:00:00 AM Return LowerDate 3/1/2020 12:00:00 AM Full Formula 3/10/1900, 7:53:30 AM Return HigherDate 4/1/2020 12:00:00 AMSolved2.9KViews0likes2Comments