Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

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 - 

ticketcreate dateclose datedateis waiting on customer?
11/2/20231/8/20231/3/2023No
11/2/20231/8/20231/4/2023No
11/2/20231/8/20231/5/2023Yes
11/2/20231/8/20231/6/2023No
11/2/20231/8/20231/7/2023No
11/2/20231/8/20231/8/2023No

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

ticketcreate dateclose datedateis waiting on customer?days since ticket creation
11/2/20231/8/20231/3/2023No1
11/2/20231/8/20231/4/2023No2
11/2/20231/8/20231/5/2023Yes2
11/2/20231/8/20231/6/2023No3
11/2/20231/8/20231/7/2023No4
11/2/20231/8/20231/8/2023No5
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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]
    )
 
data142536_0-1685588728990.png

 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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]
    )
 
data142536_0-1685588728990.png

 

tackytechtom
Super User
Super User

Hi @Anonymous ,

 

How about this:

tackytechtom_0-1685498174090.png

 

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! linkedIn

#proudtobeasuperuser 

Anonymous
Not applicable

Thank you for getting back. I tried adding another ticket data to the table and it shows the below result.

data142536_1-1685550820755.png

 

the first instance for every ticket should be the difference between create date and date.Increment should start after this point.

 

expected result

ticketcreate dateclose datedateis waiting on customer?Measure Increment
1Monday, January 2, 2023Sunday, January 8, 2023Tuesday, January 3, 2023No1
1Monday, January 2, 2023Sunday, January 8, 2023Wednesday, January 4, 2023No2
1Monday, January 2, 2023Sunday, January 8, 2023Thursday, January 5, 2023Yes2
1Monday, January 2, 2023Sunday, January 8, 2023Friday, January 6, 2023No3
1Monday, January 2, 2023Sunday, January 8, 2023Saturday, January 7, 2023No4
1Monday, January 2, 2023Sunday, January 8, 2023Sunday, January 8, 2023No5
2Tuesday, January 3, 2023Friday, January 6, 2023Wednesday, January 4, 2023No1
2Tuesday, January 3, 2023Friday, January 6, 2023Thursday, January 5, 2023Yes1
2Tuesday, January 3, 2023Friday, January 6, 2023Friday, January 6, 2023No2

 

ryan_mayu
Super User
Super User

@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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.