The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
94 | |
80 | |
63 | |
56 |
User | Count |
---|---|
248 | |
122 | |
110 | |
77 | |
70 |