DateDiff Measure

[Anonymous]

Im having trouble showing the correct calculation of days between stages.

Shown below measure / formula by DAX code.

Firstly I need to create a calculate column [Index By Q No]  to set index for every group using DAX below:

Index By Q No = RANKX(FILTER(Table1, Table1[Q No] = EARLIER(Table1[Q No])), RANKX(ALL(Table1), Table1[Last Modified]), , DESC, Dense)

Then create a calcualte column to calculate datediff which is your expected result:

DateDiff = 
VAR Previous_Date = CALCULATE(MAX(Table1[Last Modified]), FILTER(Table1, Table1[Index By Q No] = EARLIER(Table1[Index By Q No]) - 1 && Table1[Q No] = EARLIER(Table1[Q No])))
RETURN 
DATEDIFF(Previous_Date, Table1[Last Modified], DAY)

The output is as below.

The index By Q No is not in sequence and the date diff measure shows wrong calculation.

May I know which steps I did wrong ? How do I get the accurate Index By Q No in sequence ?

[v-frfei-msft]

Hi @Anonymous,

Based on my test, we can update the formulas as below to meet your requirement.

Index By Q No =
RANKX (
    FILTER ( Table1, Table1[Q No] = EARLIER ( Table1[Q No] ) ),
    RANKX ( ALL ( Table1 ), Table1[Last Modified] ),
    ,
    DESC,
    DENSE
)

Datenew =
VAR new =
    RANKX (
        FILTER ( Table1, Table1[Q No] = EARLIER ( Table1[Q No] ) ),
        RANKX ( ALL ( Table1 ), Table1[Last Modified] ),
        ,
        DESC,
        DENSE
    )
RETURN
    DATEDIFF (
        CALCULATE (
            MAX ( Table1[Last Modified] ),
            FILTER (
                Table1,
                Table1[Q No] = EARLIER ( Table1[Q No] )
                    && Table1[Index By Q No]
                    = new - 1
            )
        ),
        Table1[Last Modified],
        DAY
    )

Then we can get the result as we excepted.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/lpreyfp6opekf5t/DateDiff%20Measure.pbix?dl=0

Regards,

Frank

[Anonymous]

@v-frfei-msft

Hi Frank,

Thank you for helping out. However, my source is from Salesforce Report database, therefore the formula for rankX and Datenew below will need to be revised.

Can you let me know how to work this formula out for my case as I am seeing grey out on some part. 

Shown below.

[v-frfei-msft]

Hi @Anonymous，

What is Opp in your formula? And the EARLIER function could not be used in measure, it only could only be used in calculated column. Could you please share your pbix or sample data to me?

Regards,

Frank

[Anonymous]

Hi @v-frfei-msft

I have message you the source file and the PBIX. Its the simplified version with propietary information removed.

Please refer and checked.

Feel free to advise how the formula will work for my case.

Thank you

Looking forward to your reply.

[Anonymous]

Hi @v-frfei-msft

Let  me know if any of your revised formula works.

I came to try the formula you provided and it does not work.

Thank You

[v-frfei-msft]

Hi @Anonymous,

Based on your file that you shared. I create two calculated column using the formulas.

Index By Q No = 
RANKX (
    FILTER ( ALL(Stages), Stages[Q No] = EARLIER ( Stages[Q No] ) ),
    RANKX ( ALL ( Stages ), Stages[Last Modified] ),
    ,
    DESC,
    DENSE
)

Datenew = 
VAR new =
    RANKX (
        FILTER ( Stages, Stages[Q No] = EARLIER ( Stages[Q No] ) ),
        RANKX ( ALL ( Stages ), Stages[Last Modified] ),
        ,
        DESC,
        DENSE
    )
RETURN
    DATEDIFF (
        CALCULATE (
            MAX ( Stages[Last Modified] ),
            FILTER (
                Stages,
                Stages[Q No] = EARLIER ( Stages[Q No] )
                    && Stages[Index By Q No]
                    = new - 1
            )
        ),
        Stages[Last Modified],
        DAY
    )

For more details, please check the pbix as attached.

Regards,

Frank

[Anonymous]

Hi @v-frfei-msft,

I appreciate your reply. The results that I have when I used the latest formula still show the same error.

(refer below screenshot)

Refer below screenshot. The sequence should go by Stage 0, 1, 2, 3 ,4 ,5 ,6, 7 and not by Quote Number. 

Will changes index by stage helps ? let me know the revised formula.

[Anonymous]

@v-frfei-msft

Can you let me know if you are able to solve this issue?

How should we go about sequencing with the stage of the same quote numbers?

Only then can I have the accurate Datenew.

Please advise.

Thank You

[v-frfei-msft]

Hi @Anonymous,

1,Create a calculated column

rank by Q no1 = RANKX(FILTER(ALL(Stages),Stages[Q No]=EARLIER(Stages[Q No])),Stages[Last Modified],,ASC,Dense)

2. Create a measure.

datediff = 
var dateto = MAX(Stages[Last Modified])
var datee = CALCULATE(MAX(Stages[Last Modified]),FILTER(ALLEXCEPT(Stages,Stages[Q No]),Stages[rank by Q no1]=MAX(Stages[rank by Q no1])-1))
return 
DATEDIFF(datee,dateto,DAY)

Also please find the pbix as attached.

Regards,

Frank

[Anonymous]

  Hi @v-frfei-msft

Yet again the formula still produce an error results.

Refer below screenshot. Can we do a screen sharing and connect via skype session ?

This would speed up the process, and getting the right formula accurate soonest.

[Anonymous]

@v-frfei-msft 

May I have your answer and corrected solution soonest?

I will need the accurate measure soonest.

Thank You

[Anonymous]

Hi @v-frfei-msft,

Let me know when you have the solutions.

Appreciate your soonest reply.

Thank You