Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi, I am trying to write a DAX measure for the below requirement . I have a table like the below -
ticket | create date | close date | date | is waiting on customer? |
1 | 1/2/2023 | 1/8/2023 | 1/3/2023 | No |
1 | 1/2/2023 | 1/8/2023 | 1/4/2023 | No |
1 | 1/2/2023 | 1/8/2023 | 1/5/2023 | Yes |
1 | 1/2/2023 | 1/8/2023 | 1/6/2023 | No |
1 | 1/2/2023 | 1/8/2023 | 1/7/2023 | No |
1 | 1/2/2023 | 1/8/2023 | 1/8/2023 | No |
it has the ticket number, create date , close date and a column containing date values between these 2 dates.
I want to create a measure that returns a column to show days since ticket creation and return the below result. This value is first calculated as the difference between create date and date for a ticket. When is waiting on customer = "Yes" , it means the ticket was sent back to the customer with some questions. So the value should not be incremented. If the next line is waiting on customer = no, then the value must be incremented by 1
ticket | create date | close date | date | is waiting on customer? | days since ticket creation |
1 | 1/2/2023 | 1/8/2023 | 1/3/2023 | No | 1 |
1 | 1/2/2023 | 1/8/2023 | 1/4/2023 | No | 2 |
1 | 1/2/2023 | 1/8/2023 | 1/5/2023 | Yes | 2 |
1 | 1/2/2023 | 1/8/2023 | 1/6/2023 | No | 3 |
1 | 1/2/2023 | 1/8/2023 | 1/7/2023 | No | 4 |
1 | 1/2/2023 | 1/8/2023 | 1/8/2023 | No | 5 |
Solved! Go to Solution.
Easier than i thought. I added a column with a default value 1 and added the below calculated column -
Easier than i thought. I added a column with a default value 1 and added the below calculated column -
Hi @Anonymous ,
How about this:
Here the DAX code:
MeasureIncrement = VAR _CustomerWaiting = SELECTEDVALUE ( 'Table'[is waiting on customer?] ) VAR _Inc = RANKX( FILTER( ALLSELECTED('Table'), 'Table'[is waiting on customer?] = "No"), CALCULATE ( MAX ('Table'[date] ), MAX ('Table'[ticket] ) = 'Table'[ticket] ), , ASC , DENSE ) RETURN IF ( _CustomerWaiting = "Yes", _Inc -1, _Inc )
Let me know if this solves the issue 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Thank you for getting back. I tried adding another ticket data to the table and it shows the below result.
the first instance for every ticket should be the difference between create date and date.Increment should start after this point.
expected result
ticket | create date | close date | date | is waiting on customer? | Measure Increment |
1 | Monday, January 2, 2023 | Sunday, January 8, 2023 | Tuesday, January 3, 2023 | No | 1 |
1 | Monday, January 2, 2023 | Sunday, January 8, 2023 | Wednesday, January 4, 2023 | No | 2 |
1 | Monday, January 2, 2023 | Sunday, January 8, 2023 | Thursday, January 5, 2023 | Yes | 2 |
1 | Monday, January 2, 2023 | Sunday, January 8, 2023 | Friday, January 6, 2023 | No | 3 |
1 | Monday, January 2, 2023 | Sunday, January 8, 2023 | Saturday, January 7, 2023 | No | 4 |
1 | Monday, January 2, 2023 | Sunday, January 8, 2023 | Sunday, January 8, 2023 | No | 5 |
2 | Tuesday, January 3, 2023 | Friday, January 6, 2023 | Wednesday, January 4, 2023 | No | 1 |
2 | Tuesday, January 3, 2023 | Friday, January 6, 2023 | Thursday, January 5, 2023 | Yes | 1 |
2 | Tuesday, January 3, 2023 | Friday, January 6, 2023 | Friday, January 6, 2023 | No | 2 |
@Anonymous
need to double check the logic.
the day is always incrased by 1?
what if we have below scenario? do you have yes for more than one day? what if the date skip some dates?
ticket | create date | date | is waiting on customer? | |
1 | 1/2/2023 | 1/3/2023 | No | 1 |
1 | 1/2/2023 | 1/4/2023 | No | 2 |
1 | 1/2/2023 | 1/5/2023 | Yes | 2 |
1 | 1/2/2023 | 1/6/2023 | Yes | ? |
1 | 1/2/2023 | 1/10/2023 | No | ? |
Proud to be a Super User!
Yes, the day is always increased by 1. There could be Yes for more than one day and there are no date skips. I have a row for every date between create date and closed date.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
107 | |
96 | |
92 | |
87 | |
69 |
User | Count |
---|---|
165 | |
130 | |
129 | |
102 | |
98 |