increment a column using DAX

[data142536]

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

[data142536 · Accepted solution]

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]

    )

[tackytechtom]

Hi @data142536 ,

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/

[data142536]

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

[ryan_mayu]

@data142536 

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	？

[data142536]

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.