Forum Discussion

Npg3's avatar
Npg3
New Member
3 years ago
Solved

Power BI time functions

Hi, 

 

I want to create a function in power bi to creates a new colum and adds a certain amount of time depending on a key word that is a period of time in a different column. 

example:

row 1  includes a date in column A and word in column B ( a word like ATO is 6 months) so when row 1 column B is ATO it adds 6 months onto row 1 column A and the new date is placed in new row 1 column C.

 

basically the amount of time added onto the row in column A is dependent on word in the corresponding row of column B

Datefunctions
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Npg3 ,

     

    You can try M Code as below.

    Date.AddMonths(
    [Column A],
    if [Column B] = "AOT" then 6 
    else if [Column B] = "XYZ" then 3 
    else if [Column B] = "ABC" then 1 
    else null)

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Npg3 

    It depends. If not too many text variants and if only number of months to be added then you may try

    Column C =
    EDATE (
    'Table'[Column A],
    SWITCH ( 'Table'[Column B], "AOT", 6, "XYZ", 3, "ABC", 1 )
    )

    • Npg3's avatar
      Npg3
      New Member
      3 years ago

      It shows expression.error: the name 'EDATE' wasn't recognized. Make sure it's spelled right

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        Npg3 

        The code is for DAX calculated column not Power Query M language.