Forum Discussion

EbyEaso's avatar
EbyEaso
Helper III
3 years ago
Solved

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...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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]
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

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

EbyEaso's avatar
EbyEaso
Helper III
3 years ago

Greg_Deckler Thank You. Appreciaet it.

 

My current calculation is this.

 

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

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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's avatar
      EbyEaso
      Helper III
      3 years ago

      It gives Syntax Error.

       

      I put my actual query name.

       

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

        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]))