Forum Discussion

Juju123's avatar
Juju123
Helper III
2 years ago
Solved

Calculate difference between date group by columns

Hi 🙂 ,  I have a table in which I want to calculate the days spent between each operation for each Workflow number, article, article designation, operation code and operation label :  For ex...
General Comment
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Juju123 

    You can refer to the following solution.

    1.Create a num measure to extract the num from operation code.

    No =
INT (
    MID (
        SELECTEDVALUE ( Table1[Operation code] ),
        SEARCH ( "E", SELECTEDVALUE ( Table1[Operation code] ),, BLANK () ) + 1,
        LEN ( SELECTEDVALUE ( Table1[Operation code] ) )
            - SEARCH ( "E", SELECTEDVALUE ( Table1[Operation code] ),, BLANK () )
    )
)

    2.Create a Rank measure

    Rank =
RANKX (
    FILTER (
        ALLSELECTED ( Table1 ),
        [Workflow number]
            IN VALUES ( Table1[Workflow number] )
                && [Article] IN VALUES ( Table1[Article] )
    ),
    [No],
    ,
    ASC
)

    3.Then calculate the differnce

    Temps entre chaque operation =
VAR a = [Rank] - 1
VAR _predate =
    MAXX (
        FILTER (
            ALLSELECTED ( Table1 ),
            [Workflow number]
                IN VALUES ( Table1[Workflow number] )
                    && [Article]
                        IN VALUES ( Table1[Article] )
                            && [Rank] = a
        ),
        [Operation Date]
    )
RETURN
    IF (
        DATEDIFF ( _predate, MAX ( Table1[Operation Date] ), DAY ) <> BLANK (),
        DATEDIFF ( _predate, MAX ( Table1[Operation Date] ), DAY ),
        0
    )

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Juju123 

You can refer to the following solution.

1.Create a num measure to extract the num from operation code.

No =
INT (
    MID (
        SELECTEDVALUE ( Table1[Operation code] ),
        SEARCH ( "E", SELECTEDVALUE ( Table1[Operation code] ),, BLANK () ) + 1,
        LEN ( SELECTEDVALUE ( Table1[Operation code] ) )
            - SEARCH ( "E", SELECTEDVALUE ( Table1[Operation code] ),, BLANK () )
    )
)

2.Create a Rank measure

Rank =
RANKX (
    FILTER (
        ALLSELECTED ( Table1 ),
        [Workflow number]
            IN VALUES ( Table1[Workflow number] )
                && [Article] IN VALUES ( Table1[Article] )
    ),
    [No],
    ,
    ASC
)

3.Then calculate the differnce

Temps entre chaque operation =
VAR a = [Rank] - 1
VAR _predate =
    MAXX (
        FILTER (
            ALLSELECTED ( Table1 ),
            [Workflow number]
                IN VALUES ( Table1[Workflow number] )
                    && [Article]
                        IN VALUES ( Table1[Article] )
                            && [Rank] = a
        ),
        [Operation Date]
    )
RETURN
    IF (
        DATEDIFF ( _predate, MAX ( Table1[Operation Date] ), DAY ) <> BLANK (),
        DATEDIFF ( _predate, MAX ( Table1[Operation Date] ), DAY ),
        0
    )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

  • Juju123's avatar
    Juju123
    Helper III
    2 years ago

    Hi Anonymous ,

     

    Thanks for your solution. 

    I have an error of syntax in last step : 

     

    Temps entre chaque operation = 
VAR a = [Rank] - 1
VAR _predate =
    MAXX (
        FILTER (
            ALLSELECTED ( CAGC_ADVL_INDICATEURS_ETAPES_WF_REP ),
            CAGC_ADVL_INDICATEURS_ETAPES_WF_REP[Numéro du workflow]
                IN VALUES ( CAGC_ADVL_INDICATEURS_ETAPES_WF_REP[Numéro du workflow])
                    && CAGC_ADVL_INDICATEURS_ETAPES_WF_REP[Désignation du workflow]
                        IN VALUES ( CAGC_ADVL_INDICATEURS_ETAPES_WF_REP[Désignation du workflow])
                            && [Rank] = a
        ),
        CAGC_ADVL_INDICATEURS_ETAPES_WF_REP[Date fin réelle]
    )
RETURN
    IF (
        DATEDIFF ( _predate, CAGC_ADVL_INDICATEURS_ETAPES_WF_REP[Date du jour]), DAY ) <> BLANK (),
        DATEDIFF ( _predate, CAGC_ADVL_INDICATEURS_ETAPES_WF_REP[Date du jour] ), DAY ),
        0
    )

     

     

     

     

    I want to change the calcul and do a datediff between previous date of operation and today()

     

    I try this, but it's not work : 

    Temps entre chaque operation =
    VAR a = [Rank] - 1
    VAR _predate =
    MAXX (
    FILTER (
    ALLSELECTED ( CAGC_ADVL_INDICATEURS_ETAPES_WF_REP ),
    CAGC_ADVL_INDICATEURS_ETAPES_WF_REP[Numéro du workflow]
    IN VALUES ( CAGC_ADVL_INDICATEURS_ETAPES_WF_REP[Numéro du workflow])
    && CAGC_ADVL_INDICATEURS_ETAPES_WF_REP[Désignation du workflow]
    IN VALUES ( CAGC_ADVL_INDICATEURS_ETAPES_WF_REP[Désignation du workflow])
    && [Rank] = a
    ),
    CAGC_ADVL_INDICATEURS_ETAPES_WF_REP[Date fin réelle]
    )
    RETURN
    IF (
    DATEDIFF ( _predate, today()), DAY ) <> BLANK (),
    DATEDIFF ( _predate, today() ), DAY ),
    0
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Juju123 

      Can you show the wrong place, the function can work well in my report if i changed it to today()

       

       

      Best Regards!

      Yolo Zhu

      • Juju123's avatar
        Juju123
        Helper III
        2 years ago

        Hi Anonymous , 

        Finally it's works, thanks you so much