Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DiffDate between two dates with where or IF condition

Hi DAX Community!

 

Im new with the Power BI Report Builder, and I'm trying to do following:

 

I am building a Defect Report which is finished to most part, now i want to implement a lead-time table which show the average time it takes a Defect(intern name for Bug) to go from status "created" to status "closed".

Like this : 

 

I have table(StatuschangeTable) which holds every statuschange of an issue.

 

 

As you can see this table is not distinct, for every issueID there are multiple records. And I just need the Diffrence between the changedate when the issue was created and when the issue was closed.

 

So i need a DAX statement which would do this (disregard the syntax in the following example):

 

DateDiff between (Status_changedate where Status_new = "CREATED") and (Status_changedate where Status_old <> "closed" AND Status_new = "closed")) 

 

I have googled this for quite a time but have not really found anything helpfull..

 

I hope YOU can help me with this! 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I solved my Problem by a workaround. I created a  new column with sql which gives me the dates of the specific statuschanges and then i said DateDiff(CreatedDate, InAnalysisDate, Day)  and its working now like that. 

    Then i Also created a Measure which give me an average time of all 21k issues to get 1Value 

4 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    6 years ago

    Anonymous  are you looking for this ?

     

     

    Measure :=
DATEDIFF (
    CALCULATE (
        MAX ( T[Date] ),
        FILTER ( ALLEXCEPT ( T, T[ID] ), T[Status] = "CREATED" )
    ),
    CALCULATE (
        MAX ( T[Date] ),
        FILTER ( ALLEXCEPT ( T, T[ID] ), T[Status] = "Closed" )
    ),
    DAY
)

    Sample Data

    IDDateStatus
    103531/1/2019CREATED
    103532/1/2019Verified
    103533/1/2019Planned
    103534/1/2019Closed
    103541/1/2018CREATED
    103542/1/2018Verified
    103545/1/2018Closed

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      i tried it out, doesnt seem to work.. smpa01  but in your example its working i dont know what im doing wrong 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I see you've requested a solution using DAX - would PowerQuery M be acceptable?

    I created the following Queries in the query editor: Closed, Created, and Table (with DateDiff)

     

    Closed ::

    let
    Source = StatusChangeTable,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Status_New] = "Closed"))
in
    #"Filtered Rows"

    Created ::

    let
    Source = StatusChangeTable,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Status_New] = "CREATED"))
in
    #"Filtered Rows"

    Table (with DateDiff) ::

    let
    Source = StatusChangeTable,
    #"Removed Columns" = Table.RemoveColumns(Source,{"Status_ChangeDate", "Status_Old", "Status_New"}),
    #"Merge Created" = Table.NestedJoin(#"Removed Columns",{"IssueID"}, Created,{"IssueID"}, "Created", JoinKind.LeftOuter),
    #"Merge Closed" = Table.NestedJoin(#"Merge Created", {"IssueID"}, Closed,{"IssueID"}, "Closed", JoinKind.LeftOuter),
    #"Expanded Created" = Table.ExpandTableColumn(#"Merge Closed", "Created", {"Status_ChangeDate"}, {"Created.Status_ChangeDate"}),
    #"Expanded Closed" = Table.ExpandTableColumn(#"Expanded Created", "Closed", {"Status_ChangeDate"}, {"Closed.Status_ChangeDate"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Closed", {"IssueID"}),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Duration.Days", each Duration.Days([Closed.Status_ChangeDate]-[Created.Status_ChangeDate])),
in
    #"Added Custom"

    And simply uncheck the load to report option on any intermediary tables that you don't need

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I solved my Problem by a workaround. I created a  new column with sql which gives me the dates of the specific statuschanges and then i said DateDiff(CreatedDate, InAnalysisDate, Day)  and its working now like that. 

    Then i Also created a Measure which give me an average time of all 21k issues to get 1Value