Forum Discussion

bchager's avatar
bchager
Icon for Resolver III rankResolver III
8 years ago

Calculate total work days elapsed after row ID changes

Hi All,

 

I have a data set that contains new hire request IDs and disposition dates (approval dates). I've been able to figure out how to calculate how many days it took to approve a request, using Excel, but I'm looking for guidance on how to accomplish this in Power BI. Each time the RequestID changes, I want Power BI to return the amount of time it took to approve the request and then display the calculation on the appropriate row only. If interested, please see the snag of the Excel solution attached here. Any ideas on how to get off the ground here? I'm hung up from the get-go, when trying to translate =IF(A3=A2,...) to DAX.

 

Any help is appreciated!

 

Need Help

2 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi bchager

     

    I think this calculated column gets close

     

    New Column = 
VAR DateThis = 'Table1'[DispositionDate]
VAR DateLast = 
    MINX(
        FILTER(
            'Table1',
                'Table1'[RequestID]=EARLIER('Table1'[RequestID]) &&
                'Table1'[Approver Name]<>EARLIER('Table1'[Approver Name]) &&
                'Table1'[DispositionDate]<EARLIER('Table1'[DispositionDate])
                )
                ,
        'Table1'[DispositionDate]
        )
RETURN 
    IF(
        NOT ISBLANK(DateLast),
        COUNTROWS(
            FILTER(CALENDAR(DateLast,DateThis),WEEKDAY([Date],3)<5)
            )
        )

    • bchager's avatar
      bchager
      Icon for Resolver III rankResolver III
      8 years ago

      That is extremely close, if not 100% solved. Thank you. The only situations the code may not be handling are those where there is only 1 row per request, i.e. where they were approved in 1 day. In those instances, the work days to approved is returning a blank. Hardly a criticism though; this is great. I may be able to handle the blanks.