Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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!!
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 !
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
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
Hi BDA2,
You should create calculate column, not measure.
Regards,
Jimmy Tao
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 ?
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.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |