Calculated Column based on Dates

[EbyEaso]

I'm trying to find out the Aging of a case wcich registered in our system. In my dataset I have 2 date feilds. Assigned Date, and Completed Date.

I have created a calculated column by subtracting Assigned Date from Completed Date, I got the difference between and converted that value to Whole number so I can see how many days it took to complete it. But My problem is for some case there will be no Completed date so it wont give me exact how many days it is still there without complete.

So I want if there is no Completed Date then need calculate between Assigned Date to Todays date. And If Completed Date is available then calculate between Assigned Date and Completed Date.

Can someone help me.

Thnak You.

[Greg_Deckler]

@EbyEaso Should be able to create an if statement that if the [Completed Date] = null then use DateTime.LocalNow in your calculation.

[EbyEaso]

@Greg_Deckler Thank You. Appreciaet it.

My current calculation is this.

Aging = 'SQL-DATA-UPDATED'[CompletedDate]-'SQL-DATA-UPDATED'[AssignedToDate]

[Greg_Deckler · Accepted solution]

@EbyEaso OK, so:

= if 'SQL-DATE-UPDATED'[CompletedDate] = null then DateTime.LocalNow() - 'SQL-DATA-UPDATED'[AssignedToDate] else 'SQL-DATE-UPDATED'[CompletedDate] - 'SQL-DATA-UPDATED'[AssignedToDate]

[EbyEaso]

It gives Syntax Error.

I put my actual query name.

[Greg_Deckler]

@EbyEaso Dude, you posted in the Power Query forum but it looks like you are trying to put Power Query code into a DAX calculated column. In DAX, that would be:

IF('SQL-DATE-UPDATED'[CompletedDate] = BLANK(), DATEDIFF(TODAY(),'SQL-DATA-UPDATED'[AssignedToDate],DAY), DATEDIFF('SQL-DATE-UPDATED'[CompletedDate], 'SQL-DATA-UPDATED'[AssignedToDate]))

[EbyEaso]

Sorry @Greg_Deckler 

I'm beginner. 🙂 Trying to get solve these.

in DAX also i got error.

And in Power Query I got this.

[EbyEaso]

@Greg_Deckler 

Finaly I got it. My Assigned To Date was in date time format. I think that was the issue. I changed that Date only format and added additional column to show todays date. Instead of using this

DateTime.LocalNow()

Now its fine.

Thank You @Greg_Deckler  for the valuable support. Appreciate it.