Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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...
Solved! Go to 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
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |