DiffDate between two dates with where or IF condition

[darko_ivanovic]

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! 

[darko_ivanovic · Accepted solution]

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 

[smpa01]

@darko_ivanovic  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

ID	Date	Status
10353	1/1/2019	CREATED
10353	2/1/2019	Verified
10353	3/1/2019	Planned
10353	4/1/2019	Closed
10354	1/1/2018	CREATED
10354	2/1/2018	Verified
10354	5/1/2018	Closed

[darko_ivanovic]

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

[cmeu]

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