Networkdays error

[prabhupan]

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

[v-ljerr-msft · Accepted solution]

Hi @prabhupan,

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

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]

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. 

[prabhupan]

hello @v-ljerr-msft,

Returns blank. 

[Phil_Seamark]

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

[prabhupan]

hello @Phil_Seamark,

It returns the date.