#NUM! value in EXCEL for DAX

Question

I created a Daily Run Rate with the using the following formule. The YearFrac is calculated as below:

YearFrac:=YEARFRAC(CONCATENATE("01/01/",YEAR('Activity Date'[FullDate])),'Activity Date'[FullDate])

YTD Quantity From Store:=TOTALYTD(SUM('Demand Planning'[QtyIssuedFromStore]),'Activity Date'[FullDate],ALL('Activity Date'))

DRR:= [YTD Quantity From Store]*(1/MAX('Activity Date'[YearFrac]))

I get everything just fine except for few columns where in i get #NUM! values. I am unable to convert or decode the error. Any help is appreciated. 

ActivityDate Hierarchy DRR YTD

2011-Q1	34166720	8,541,680
1 - January	33329880	2,777,490
1/1/2011 - Saturday	#NUM!	45,923
1/2/2011 - Sunday	40849200	67,547
1/3/2011 - Monday	35701560	84,872
1/4/2011 - Tuesday	34591680	89,922
1/5/2011 - Wednesday	33955380	89,018
1/6/2011 - Thursday	33366384	86,140
1/7/2011 - Friday	32682660	81,289

---

Words offer the means to meaning, and for those who will listen, the enunciation of truth - V for Vendetta.

Answer 1

 I found out the Issue. The issue is with YearFrac is as below:=YEARFRAC(CONCATENATE("01/01/",YEAR('Demand Planning'[Activity Date])),'Demand Planning'[Activity Date]).

So we have the Activity Date as '01/01/2011' and '01/01/2011'. As we all know Yearfrac gives us the date difference between two dates. If there is no date difference We are getting a 0 value. Hence the Error. Can anybody suggest any work arounds

---

Words offer the means to meaning, and for those who will listen, the enunciation of truth - V for Vendetta.

Answer 2

You just need to test to make sure that your denominator is not 0.

DRR:= IF(MAX('Activity Date'[YearFrac]) = 0, BLANK(), [YTD Quantity From Store]*(1/MAX('Activity Date'[YearFrac])))

---

http://darren.gosbell.com - please mark correct answers