Calculate difference between date group by columns

[Juju123]

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 🙂

[Anonymous · Accepted solution]

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]

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]

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]

Hi @Anonymous , 

Finally it's works, thanks you so much

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.

[Juju123]

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]

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.

[Juju123]

Hi @Ashish_Mathur ,

Thanks for your solution. 

I test it and i'm blocked in step two. 

I'm in DirectQuery Mode and i have an error when i use EARLIER in my measure. I have this error when i create a column with EARLIER : EARLIEST refers to an earlier row context that does not exist

I create a new measure and i try to replace function EARLIER by SELECTEDVALUE but it's doesent work : 

Previous date = CALCULATE(MAX(CAGC_ADVL_INDICATEURS_ETAPES_WF_REP[WF - Date fin reelle]),FILTER('CAGC_ADVL_INDICATEURS_ETAPES_WF_REP',CAGC_ADVL_INDICATEURS_ETAPES_WF_REP[WF - Numéro du workflow]=SELECTEDVALUE('CAGC_ADVL_INDICATEURS_ETAPES_WF_REP'[WF - Numéro du workflow])&&CAGC_ADVL_INDICATEURS_ETAPES_WF_REP[Designation article]=SELECTEDVALUE('CAGC_ADVL_INDICATEURS_ETAPES_WF_REP'[Designation article])&&CAGC_ADVL_INDICATEURS_ETAPES_WF_REP[Numero article externe]=SELECTEDVALUE('CAGC_ADVL_INDICATEURS_ETAPES_WF_REP'[Numero article externe])&&CAGC_ADVL_INDICATEURS_ETAPES_WF_REP[OPERATION NUMBER]<SELECTEDVALUE('CAGC_ADVL_INDICATEURS_ETAPES_WF_REP'[OPERATION NUMBER])))

[Ashish_Mathur]

Mine are calculated column formulas - not measures.

[Juju123]

Yes i try column formula like this, but it's doesn't work too.

Do you work on Power BI desktop or DirectQuery Mode ?

I got this error message when i create a column formula in DirectQueryMode : 

The "CALCULATE" function is not allowed as part of the DAX column calculated expressions in DirectQuery models.

Maybe I don't have the column in the right place?

[ryan_mayu]

@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

[Juju123]

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]

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.

[Juju123]

@ryan_mayu 

Thank you for the explanations and I understand better what the "Index" column is for.

The result is to have the difference in days for each operation.

Example for the first operation the number of days will be 0 days. This is why I put 05/11/2018 - 0
For the second operation the calculation will be 05/11/2018 - 19/12/2018 and so on.

Have I been clear ?

[ryan_mayu]

@Juju123 

it's clear now. then you can modify the DAX

pls see the attachment below

[Juju123]

@ryan_mayu,

Thank you very much for your explanations and for the time you took to explain to me.

I will test your solution and I will come back to you to tell you if it is ok or not.

[Juju123]

Hi @ryan_mayu ,

I try your solution and it's doesn't work because i'm in DirectQuery mode. I can't add an index column. 

I have this error message : 

hanks you. I try to add a column index but i'm in direct query and it's doesn't work, i have this message : This step generates a query that is not supported in DirectQuery mode

[ryan_mayu]

do you have anyother column that we can refer to its order?

[Juju123]

@ryan_mayu 

I added a column which is the operation number.

I modified this formula to retrieve the previous date for each Workflow/Article pair:

Previous_Date = MAXX(FILTER(ALL('CAGC_ADVL_INDICATEURS_ETAPES_WF_REP'),CAGC_ADVL_INDICATEURS_ETAPES_WF_REP[Numero de l'operation]<SELECTEDVALUE('CAGC_ADVL_INDICATEURS_ETAPES_WF_REP'[Numero de l'operation])&&[Designation article]=SELECTEDVALUE('CAGC_ADVL_INDICATEURS_ETAPES_WF_REP'[Designation article])),CAGC_ADVL_INDICATEURS_ETAPES_WF_REP[WF - Date fin reelle])

I can recover the previous date but not always.
How can I fix this problem

Merci 

[ryan_mayu]

@Juju123 

pls see the attachment below