Forum Discussion
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 ta...
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
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