topic Re: Power BI time functions in DAX Commands and Tips

Power BI time functions

Npg3 — Sun, 06 Aug 2023 16:16:10 GMT

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

Re: Power BI time functions

tamerj1 — Sun, 06 Aug 2023 17:30:56 GMT

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

Re: Power BI time functions

Npg3 — Sun, 06 Aug 2023 17:50:10 GMT

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

Re: Power BI time functions

tamerj1 — Sun, 06 Aug 2023 18:56:36 GMT

Npg3

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

Re: Power BI time functions

Npg3 — Sun, 06 Aug 2023 18:58:07 GMT

How can I create this function in a custom column in power BI?

Re: Power BI time functions

tamerj1 — Sun, 06 Aug 2023 19:36:10 GMT

Npg3

You may insert conditional column. Should be straightforward.

Re: Power BI time functions

Anonymous — Tue, 08 Aug 2023 06:19:01 GMT

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.

Re: Power BI time functions

Npg3 — Tue, 08 Aug 2023 13:43:13 GMT

This works but the problem is if I want column B to have AOT and ABC in one row it does not work. The way the data is pulled in is in some rows of columb B they have multiple (codes). How do I have power bi choose the code with the lowest timeframe. I tried using LOOKUPVALUE but it will not recognize LOOKUPVALUE people have been using power querey for that function which I do not have. Any advice?