Forum Discussion

prabhupan's avatar
prabhupan
Helper I
9 years ago
Solved

Networkdays error

hello, 

 

as suggested in earlier discussions for calculating the networkdays / weekdays between dates; i created a table "Calendar" and then a flag "Check" which will be 1 for weekdays and 0 otherwise. 

 

i know tried to create a column as below but the error "an invalid numeric representation of a date value was encountered". 

 

 

DaysGiven2 = var New1 = CALCULATE(MAXX(RRTRequestsStatusChanges,RRTRequestsStatusChanges[Date]),FILTER(RRTRequestsStatusChanges,RRTRequestsStatusChanges[New]="1 New"),FILTER(RRTRequests,RRTRequests[RequestID]=RRTRequestsStatusChanges[FiD])) return CALCULATE(SUM('Calendar'[Check]),DATESBETWEEN('Calendar'[CalendarDate],RRTRequestsStatusChanges[StartDate],New1))

 

Let me know what wrong... 

Need Help
Tips and Tricks
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi prabhupan,

     

    Could you try the formula below to see if it works?:smileyhappy:

    DaysGiven2 =
VAR New1 =
    CALCULATE (
        MAXX ( RRTRequestsStatusChanges, RRTRequestsStatusChanges[Date] ),
        FILTER ( RRTRequestsStatusChanges, RRTRequestsStatusChanges[New] = "1 New" ),
        FILTER ( RRTRequests, RRTRequests[RequestID] = RRTRequestsStatusChanges[FiD] )
    )
RETURN
    CALCULATE (
        SUM ( 'Calendar'[Check] ),
        FILTER (
            Calendar,
            'Calendar'[CalendarDate] >= RRTRequestsStatusChanges[StartDate]
                && 'Calendar'[CalendarDate] <= New1
        )
    )

     

    Regards

5 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi prabhupan,

     

    Could you try the formula below to see if it works?:smileyhappy:

    DaysGiven2 =
VAR New1 =
    CALCULATE (
        MAXX ( RRTRequestsStatusChanges, RRTRequestsStatusChanges[Date] ),
        FILTER ( RRTRequestsStatusChanges, RRTRequestsStatusChanges[New] = "1 New" ),
        FILTER ( RRTRequests, RRTRequests[RequestID] = RRTRequestsStatusChanges[FiD] )
    )
RETURN
    CALCULATE (
        SUM ( 'Calendar'[Check] ),
        FILTER (
            Calendar,
            'Calendar'[CalendarDate] >= RRTRequestsStatusChanges[StartDate]
                && 'Calendar'[CalendarDate] <= New1
        )
    )

     

    Regards

    • prabhupan's avatar
      prabhupan
      Helper I
      9 years ago

      hello v-ljerr-msft,

       

      Infact it works with a slight change. 

       

      DaysGiven2 =
      VAR New1 =
      CALCULATE (
      MAXX ( RRTRequestsStatusChanges, RRTRequestsStatusChanges[Date] ),
      FILTER ( RRTRequestsStatusChanges, RRTRequestsStatusChanges[New] = "1 New" ),
      FILTER ( RRTRequests, RRTRequests[RequestID] = RRTRequestsStatusChanges[FiD] )
      )
      RETURN
      CALCULATE (
      SUM ( 'Calendar'[Check] ),
      FILTER (
      'Calendar',
      'Calendar'[CalendarDate] >= New1
      && 'Calendar'[CalendarDate] <= RRTRequestsStatusChanges[StartDate]
      )
      )

       

       

      Thanks for your suggestion. :smileyhappy:

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Hi prabhupan,

     

    Just trying to understand where your error is.

     

    What do you get when you run just the following

     

    DaysGiven2 test =
VAR New1 =
    CALCULATE (
        MAXX ( RRTRequestsStatusChanges, RRTRequestsStatusChanges[Date] ),
        FILTER ( RRTRequestsStatusChanges, RRTRequestsStatusChanges[New] = "1 New" ),
        FILTER ( RRTRequests, RRTRequests[RequestID] = RRTRequestsStatusChanges[FiD] )
    )

Return New1