Forum Discussion
Calculated Column based on Dates
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.
Anonymous OK, so:
= if 'SQL-DATE-UPDATED'[CompletedDate] = null then DateTime.LocalNow() - 'SQL-DATA-UPDATED'[AssignedToDate] else 'SQL-DATE-UPDATED'[CompletedDate] - 'SQL-DATA-UPDATED'[AssignedToDate]
7 Replies
- Greg_Deckler
Community Champion
Anonymous Should be able to create an if statement that if the [Completed Date] = null then use DateTime.LocalNow in your calculation.
Greg_Deckler Thank You. Appreciaet it.
My current calculation is this.
Aging = 'SQL-DATA-UPDATED'[CompletedDate]-'SQL-DATA-UPDATED'[AssignedToDate]- Greg_Deckler
Anonymous OK, so:
= if 'SQL-DATE-UPDATED'[CompletedDate] = null then DateTime.LocalNow() - 'SQL-DATA-UPDATED'[AssignedToDate] else 'SQL-DATE-UPDATED'[CompletedDate] - 'SQL-DATA-UPDATED'[AssignedToDate]