cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
richardmoreno Helper II
Helper II

EARLIER chalenge

Hello Power friends,

 

I have a table with IT tickets like that:

Id_ticket, date, old_status, new_status,days_to_fix

100,01/01/2018,<blank>,"new ticket" ,0 // user report a problem opening a ticket

100,01/01/2018,"new ticket","working",0 // IT specialist start working on it

100,01/03/2018,"working","fixed", 2 // IT specialist solved it in two days

100,01/04/2018,"fixed" , "working",0 // user was not satisfied with solution and put the ticket on working status again

100,01/09/2018,"working","fixed", 5 // IT specialist got new solution in five days

100,01/10/2018,"fixed","closed", 0 // User accept this solution

 

The question is: "How can I calculate the "days_to_fix" collum in this example? Note that It has to be filled every time we have a "fixed" on new_status collum.

 

Thanks for your time and knowledge sharing.

1 ACCEPTED SOLUTION

Accepted Solutions
Chihiro Solution Sage
Solution Sage

Re: EARLIER chalenge

Greg gave you the basic construct, and link explaining the concept.

 

You'll just need to change RETURN portion to only return result when [new_status]="fixed".

 

So...

 

Column =
VAR __max =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            [Id_ticket] = EARLIER ( [Id_ticket] )
                && [date] < EARLIER ( [date] )
                && [new_status] = EARLIER ( [old_status] )
        ),
        [date]
    )
RETURN
    IF ( [new_status] = "fixed", [date] - __max, 0 )

Result0.JPG

View solution in original post

6 REPLIES 6
richardmoreno Helper II
Helper II

EARLIER chalenge for Power users

Hello Power friends,

 

I have a table with IT tickets like that:

Id_ticket, date, old_status, new_status,days_to_fix

100,01/01/2018,<blank>,"new ticket" ,0                // user report a problem opening a ticket

100,01/01/2018,"new ticket","working",0               // IT specialist start working on it

100,01/03/2018,"working","fixed", 2                      // IT specialist solved it in two days

100,01/04/2018,"fixed" , "working",0                  // user was not satisfied with solution and put the ticket on working status again

100,01/09/2018,"working","fixed", 5                    // IT specialist got new solution in five days

100,01/10/2018,"fixed","closed", 0                       // User accept this solution

 

The question is: "How can I calculate the "days_to_fix" collum in this example? Note that It has to be filled every time we have a "fixed" on new_status collum. 

 

Thanks for your time and knowledge sharing.

Highlighted
Super User IV
Super User IV

Re: EARLIER chalenge

It is going to look something like:

 

Column =
VAR __max = MAXX(FILTER(ALL('Table'),[Id_ticket]=EARLIER([Id_ticket])&&[date]<EARLIER([date])&&[new_status]=EARLIER([old_status])),[date])
RETURN [date] - __max

See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

richardmoreno Helper II
Helper II

Re: EARLIER chalenge

Thanks for your time @Greg_Deckler, but, unfortunately, it didn't worked as expected. If you could check, I´ll be thankfull.

The new collum had a unknown value in the first line (the new ticket) and in the 2nd "working" line.

 

Thanks.

Chihiro Solution Sage
Solution Sage

Re: EARLIER chalenge

Greg gave you the basic construct, and link explaining the concept.

 

You'll just need to change RETURN portion to only return result when [new_status]="fixed".

 

So...

 

Column =
VAR __max =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            [Id_ticket] = EARLIER ( [Id_ticket] )
                && [date] < EARLIER ( [date] )
                && [new_status] = EARLIER ( [old_status] )
        ),
        [date]
    )
RETURN
    IF ( [new_status] = "fixed", [date] - __max, 0 )

Result0.JPG

View solution in original post

Super User IV
Super User IV

Re: EARLIER chalenge

Right, I was just trying to give you the general jist of where it was headed. I didn't actually test that code so it's a small miracle it worked at all without some type of syntax error! 🙂 I only had a couple minutes to respond. Not surprising that you'd get an error for the first row outlier. Probably fixable by checking how many rows are returned using COUNTROWS.

 

Let me see if I can find some time to get your data loaded and write it out specifically with that data.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

richardmoreno Helper II
Helper II

Re: EARLIER chalenge

Thanks a lot @Greg_Deckler and @Chihiro. It worked very well. 

 

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors