cancel
Showing results for
Did you mean:
Frequent Visitor

## increment a column using DAX

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
1 ACCEPTED SOLUTION
Frequent Visitor

Easier than  i thought. I added a column with a default value 1 and added the below calculated column -

TotalQuantity1 =
SUMX(
FILTER(
ALL('Table'),
'Table'[ticket] = EARLIER('Table'[ticket]) &&
'Table'[date] <= EARLIER('Table'[date]) &&
'Table'[is waiting on customer?] = "No"
),
'Table'[Custom]
)

5 REPLIES 5
Frequent Visitor

Easier than  i thought. I added a column with a default value 1 and added the below calculated column -

TotalQuantity1 =
SUMX(
FILTER(
ALL('Table'),
'Table'[ticket] = EARLIER('Table'[ticket]) &&
'Table'[date] <= EARLIER('Table'[date]) &&
'Table'[is waiting on customer?] = "No"
),
'Table'[Custom]
)

Super User

Hi @data142536 ,

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 🙂

Proud to be a Super User!

Frequent Visitor

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

Super User

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!

Frequent Visitor

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.