cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LukeWatsonAJG
Frequent Visitor

Tiempo de resolución excluyendo los fines de semana

Hola

He leído a través de varios temas rodean esto sin embargo no he sido capaz de reproducir o conseguir que esto funcione.

Necesito calcular la diferencia de fecha entre dos fechas excluyendo un fin de semana.

Tengo dos mesas con una relación entre Fecha y Fecha resuelta.

¿Cómo calculo la diferencia entre la fecha de última resuelta y la fecha de envío, excluyendo los fines de semana.

DimDate

1.PNG

ResolvedINC2.PNG

3 REPLIES 3
Icey
Community Support
Community Support

Hola @LukeWatsonAJG ,

Si desea contar los días laborables, cree una medida así:

Measure =
CALCULATE (
    COUNT ( DimDate[Date] ) + 0,
    FILTER (
        DimDate,
        [Is Weekend] = 0
            && [Date] >= DATEVALUE ( MAX ( ResolvedINC[Submit date] ) )
            && [Date] <= DATEVALUE ( MAX ( ResolvedINC[Resolved Date] ) )
    )
)

Si desea calcular el tiempo específico, intente crear una medida así:

Measure 2 = 
VAR MinWeekend =
    CALCULATE (
        MIN ( DimDate[Date] ),
        FILTER (
            DimDate,
            DimDate[Is Weekend] = 1
                && DimDate[Date] >= DATEVALUE ( MAX ( ResolvedINC[Submit date] ) )
                && DimDate[Date] <= DATEVALUE ( MAX ( ResolvedINC[Resolved Date] ) )
        )
    )
VAR MaxWeekend =
    CALCULATE (
        MAX ( DimDate[Date] ),
        FILTER (
            DimDate,
            DimDate[Is Weekend] = 1
                && DimDate[Date] >= DATEVALUE ( MAX ( ResolvedINC[Submit date] ) )
                && DimDate[Date] <= DATEVALUE ( MAX ( ResolvedINC[Resolved Date] ) )
        )
    )
VAR MaxWeekend_ =
    IF ( MaxWeekend <> BLANK (), MaxWeekend + 1 )
VAR DateDiff1 =
    DATEDIFF (
        MAX ( ResolvedINC[Submit date] ),
        MAX ( ResolvedINC[Resolved Date] ),
        SECOND
    )
VAR DateDiff2 =
    DATEDIFF ( MAX ( ResolvedINC[Submit date] ), MinWeekend, SECOND )
        + DATEDIFF ( MaxWeekend_, MAX ( ResolvedINC[Resolved Date] ), SECOND )
VAR DateDiff3 =
    DATEDIFF ( MaxWeekend_, MAX ( ResolvedINC[Resolved Date] ), SECOND )
VAR DateDiff_ =
    IF (
        MinWeekend = BLANK (),
        DateDiff1,
        IF (
            MAX ( ResolvedINC[Submit date] ) < MinWeekend
                && MAX ( ResolvedINC[Resolved Date] ) > MaxWeekend_,
            DateDiff2,
            IF (
                MAX ( ResolvedINC[Submit date] ) >= MinWeekend
                    && MAX ( ResolvedINC[Submit date] ) <= MaxWeekend_,
                DateDiff3
            )
        )
    )
VAR Days =
    TRUNC ( DateDiff_ / 24 / 3600 )
VAR Hours =
    TRUNC ( ( DateDiff_ - Days * 24 * 3600 ) / 3600 )
VAR Minutes =
    TRUNC ( ( DateDiff_ - Hours * 3600 - Days * 24 * 3600 ) / 60 )
VAR Seconds = DateDiff_ - Minutes * 60 - Hours * 3600 - Days * 24 * 3600
VAR Result =
    Days & ":"
        & FORMAT ( Hours, "00" ) & ":"
        & FORMAT ( Minutes, "00" ) & ":"
        & FORMAT ( Seconds, "00" )
RETURN
    Result

datediff.PNG

BTW, archivo .pbix adjunto.

Saludos

Icey

Si este post ayuda,entonces considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

amitchandak
Super User IV
Super User IV

@LukeWatsonAJG , Consulte este archivo. Tiene diferencia de fecha entre dos fechas en dos tablas. Compruebe la página 2/Workday diff

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@Icey @amitchandak

Estoy luchando con el mismo problema y me hizo tratar de medidas mencionadas y otros DAX, pero no hay suerte aquí estoy compartiendo con usted una muestra del informe sería una gran ayuda si usted puede ayudar

https://1drv.ms/u/s!Aj7YLBYfrzPkil3VcmfrgIPNio5c?e=Lat0Jo

COuntdays ? COUNTROWS(FILTER(ALL(Datetable),Datetable[Date]>- SELECTEDVALUE(Table1[Created On]) && Datetable[Date] <-SELECTEDVALUE(Table1[Solved Date]) && NOT(Datetable[WeekDayNum] en el número 6,7o) && Datetable[Isingday]
Medir: VAR _st , MAX(Table1[Created On]) VAR _end- SWITCH(MAX( Table1[Status]),"resolved" , MAX(Table1[Fecha de resolución]), "activo", HOY(), _st) REturn COUNTROWS(FILTER(Datetable,_st <- Datetable[Date] &&Datetable[Date] <- _end && WEEKDAY(Datetable[Date],2) <6))

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.