# #NUM! value in EXCEL for DAX

### Domanda

• 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.

• Modificato martedì 10 aprile 2012 22:02
martedì 10 aprile 2012 22:01

### Risposte

• 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])))

• Proposto come risposta giovedì 12 aprile 2012 02:20
• Contrassegnato come risposta giovedì 12 aprile 2012 02:22
mercoledì 11 aprile 2012 03:54

### Tutte le risposte

•  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.

martedì 10 aprile 2012 22:29
• 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])))