none
#NUM! value in EXCEL for DAX

    Pregunta

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


    • Editado san463 martes, 10 de abril de 2012 22:02
    martes, 10 de abril de 2012 22:01

Respuestas

  • 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

    • Propuesto como respuesta Challen FuModerator jueves, 12 de abril de 2012 2:20
    • Marcado como respuesta san463 jueves, 12 de abril de 2012 2:22
    miércoles, 11 de abril de 2012 3:54

Todas las respuestas

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

    martes, 10 de abril de 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])))


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

    • Propuesto como respuesta Challen FuModerator jueves, 12 de abril de 2012 2:20
    • Marcado como respuesta san463 jueves, 12 de abril de 2012 2:22
    miércoles, 11 de abril de 2012 3:54