Measure for DateDiff

[Anonymous]

Can someone help me out with the datediff measure for a list of different Stages and different Quotes

Have a look at the screenshot below

[Thejeswar]

Hi @Anonymous,

DateDiff should be used as a Column, not a measure

Difference = DATEDIFF(Table1[Last Modified], TODAY(), DAY)

If this solves, accept the solution and give a kudos!!

[Anonymous]

@Thejeswar

Your measure does not give an accurate result. See the screenshot below.

I need the first row in the (Diffrence Collumn) of every new Q No to be empty; start of new start stage change, just like my ealier posting !

[v-yuta-msft]

Hi BDA2,

To achieve your requirement, firstly you 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)

 

Hope it's helpful to you.

Jimmy Tao

[Anonymous]

Hi @v-yuta-msft,

Appreciate your sharing.

However, when I used measure for Index By Q No, I have multiple tables. I have trouble finding EARLIER function in my table and "Dense" is gray out. Refer photo screenshot:-

Once I solve the above Index By Q No, only can I used measure for DateDiff.

Let me know your thoughts

Thank You

[Anonymous]

Hi @v-yuta-msft

Let  me know if you have a way to solve my queries 

[v-yuta-msft]

Hi BDA2,

You should create calculate column, not measure.

Regards,

Jimmy Tao

[Anonymous]

@v-yuta-msft

It works thank you

[Anonymous]

hi @v-yuta-msft

Apparently I came to notice that the formula applies if my stages are distinct/not duplicated.

How should I get the same output for multiple duplicated stages for the same quote ?

[Anonymous]

@v-yuta-msft @Thejeswar

I am still having error for other dashboard I build and the formula does not seems to be accurate. Refer below screenshot.

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