Forum Discussion

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 example for WF00000002 and article 123456, the calcul it's :

05/11/2018 - 0

19/12/2018 - 05/11/2018

15/11/2018 - 19/12/2018 etc....

I tried to implement this formula but it doesn't work :

Temps entre chaque operation = 
VAR currentcode =
    MAX ( Table1[Workflow num] )
VAR currentdate =
    MAX ( Table1[Operation date] )
VAR previousdate =
    MAXX (
        FILTER (
            ALL ( Table1),
            Table1[Workflow num]] = currentcode
                && Table1[Operation date] < currentdate
        ),
        Table1[Operation date]
    )
RETURN
    IF (
        HASONEVALUE ( Table1[Workflow num] ),
        INT ( currentdate - previousdate )
            * DIVIDE ( previousdate, previousdate )
    )

CSV file :

Workflow number;Article;Designation article;Operation code;Operation label;Operation date
WF0000000002;123456;STICK;OPE020;;05/11/2018
WF0000000002;123456;STICK;OPE070;;19/12/2018
WF0000000002;123456;STICK;OPE090;;15/11/2018
WF0000000002;123456;STICK;OPE099;;13/12/2018
WF0000000002;123456;STICK;OPE110;;07/12/2018
WF0000000002;123456;STICK;OPE140;;07/12/2018
WF0000000002;123456;STICK;OPE150;;07/12/2018
WF0000000002;741852;ON BOARD;OPE020;;01/03/2018
WF0000000002;741852;ON BOARD;OPE070;;05/06/2018
WF0000000002;741852;ON BOARD;OPE090;;10/09/2018
WF0000000002;741852;ON BOARD;OPE099;;13/09/2018
WF0000000002;741852;ON BOARD;OPE110;;20/09/2018
WF0000000002;741852;ON BOARD;OPE140;;25/10/2018
WF0000000002;741852;ON BOARD;OPE150;;30/10/2018
WF0000000002;741852;ON BOARD;OPE160;;05/11/2018

Thanks you in advance for your help 🙂

General Comment

Need Help

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.

Calculate difference between date group by columns.pbix42 KB

21 Replies

ryan_mayu
Super User
2 years ago
Juju123

you can create an index column in PQ and use DAX to create a column

Column = VAR _last=maxx(FILTER('Table','Table'[Workflow number]=EARLIER('Table'[Workflow number])&&'Table'[Article]=EARLIER('Table'[Article])&&'Table'[Index]=EARLIER('Table'[Index])-1),'Table'[Operation date]) return if(ISBLANK(_last), 'Table'[Operation date] ,'Table'[Operation date] -_last)

pls see the attachment below
Calculate difference between date group by columns.pbix19 KB
- Juju123
  Helper III
  2 years ago
  Hi ryan_mayu ,
  
  Thanks for your feedback.
  I'm beignner on Power BI and I would like to understand what you have.
  
  If I understand correctly, you first created an Index to sort the dates.
  
  Secondly, do you calculate the difference between each operation for each Workflow, article, article designation and Operation code?
  
  On the other hand, I don't understand why the new column is in date format instead of being in number format? Because the goal is to calculate the difference between each operation, we should therefore have numbers in this column unless I have not understood your process.
  That's right ?
  - ryan_mayu
    Super User
    2 years ago
    not sort the dates, just sort row orders. i don't think the calculation is following the date order.
    
    You can change the data type to number format. However, what's the expected output for "05/11/2018 - 0" ? also be a number? That will be a very huge number.
    
    So pls confirm what's the data type will be. you can either set it as a number or a date.
Ashish_Mathur
Super User
2 years ago
Hi,

Share data in a format that can be pasted in an MS Excel file.
- Juju123
  Helper III
  2 years ago
  Hi Ashish_Mathur ,
  
  I create a csv file, it's ok for you ?
  Workflow number;Article;Designation article;Operation code;Operation label;Operation date
  WF0000000002;123456;STICK;OPE020;;05/11/2018
  WF0000000002;123456;STICK;OPE070;;19/12/2018
  WF0000000002;123456;STICK;OPE090;;15/11/2018
  WF0000000002;123456;STICK;OPE099;;13/12/2018
  WF0000000002;123456;STICK;OPE110;;07/12/2018
  WF0000000002;123456;STICK;OPE140;;07/12/2018
  WF0000000002;123456;STICK;OPE150;;07/12/2018
  WF0000000002;741852;ON BOARD;OPE020;;01/03/2018
  WF0000000002;741852;ON BOARD;OPE070;;05/06/2018
  WF0000000002;741852;ON BOARD;OPE090;;10/09/2018
  WF0000000002;741852;ON BOARD;OPE099;;13/09/2018
  WF0000000002;741852;ON BOARD;OPE110;;20/09/2018
  WF0000000002;741852;ON BOARD;OPE140;;25/10/2018
  WF0000000002;741852;ON BOARD;OPE150;;30/10/2018
  WF0000000002;741852;ON BOARD;OPE160;;05/11/2018
  - Ashish_Mathur
    Super User
    2 years ago
    Write these calculated column formulas
    
    Operation number = 1*(RIGHT(Data[Operation code],3))
    Previous date = CALCULATE(MAX(Data[Operation date]),FILTER(Data,Data[Workflow number]=EARLIER(Data[Workflow number])&&Data[Article]=EARLIER(Data[Article])&&Data[Designation article]=EARLIER(Data[Designation article])&&Data[Operation number]<EARLIER(Data[Operation number])))
    Difference = if(ISBLANK(Data[Previous date]),BLANK(),1*(Data[Operation date]-Data[Previous date]))
    
    Hope this helps.

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.

Calculate difference between date group by columns.pbix42 KB

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

Forum Discussion

Calculate difference between date group by columns

21 Replies

Related content

Dynamic Calculation Different Period

Different Calculations

1. How to add two different calculated columns of two different table

Calculating Differences

Calculating day/time difference