cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

dateadd function today minus period

Hi all,

 

I'm new to Power BI, but know my way around in SQL.

I'm trying to create a calculated column / measure with an if statement.

What I want to achieve is this:

 

if maxdate is larger than today - 3 months, then value 1 else value 0.

This should be very easy to do in my opnion

 

Column = if(Query1[maxdate]>dateadd(TODAY(),-3,MONTH),"1","0")

But I get the error: The first argument to DATEADD must specify a column.

Why isn't it possible to use today() ?

And how should I solve this?

(I tried it as a measure as well, but again to no avail.)

 

Could someone please help me?

I know I can solve it easily in my SQL query, but I want to understand why this doesn't work or how to solve this?

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Resolver IV
Resolver IV

Re: dateadd function today minus period

Table.Addcolumn needs a table parameter where you want to add the column. In my case the table is called '"Changed Type" which is the name of my last transformation step before Table.AddColumn.

 

Your approach was just by using the button "custom column" So there you just write 

 

if [EndDate]<Date.AddMonths(Date.From(DateTime.LocalNow()),-3) then 1 else 0)

The rest will be added by Query Editor itself. 

View solution in original post

Highlighted
Resolver IV
Resolver IV

Re: dateadd function today minus period

So when you click on the word error he will show you some details about it.

 

However

 

You say that your date column is in datetime format? Could you please write Date.From([lastLD]) instead of [lastLD]

 

Brackets are ok as far as i see

View solution in original post

9 REPLIES 9
Highlighted
Resolver IV
Resolver IV

Re: dateadd function today minus period

hi @WillemC

 

unfortunately it is different from sql language. In your case I would prefer Power Query to do the job.

 

You can add a userdefined column

 

example.jpg

Highlighted
Resolver I
Resolver I

Re: dateadd function today minus period

okay, help me out a little bit.

Where do I put this power query?

 

I'm in the Query editor, under Add Column I see different types.

Do I need a Custom Column or Conditional Column? Or something else?

 

The query itself is clear to me.

 

Highlighted
Resolver IV
Resolver IV

Re: dateadd function today minus period

custom column

 

all other options are in fact custom columns as well. But they help you to get your wishes without writing code.

 

(conditional e.g. is the click variant of if then else)

Highlighted
Resolver I
Resolver I

Re: dateadd function today minus period

Thank you.

Just one small question, what does the #"Changed Type" mean?

Since it's throwing me an error.

Quick Google search didn't give me an answer right away.

 

edit:

seems that's the table name on which you want to add the column.

 

Highlighted
Resolver I
Resolver I

Re: dateadd function today minus period

The expression is giving me the following error:

Expression.Error: Evaluation resulted in a stack overflow and cannot continue.

Highlighted
Resolver IV
Resolver IV

Re: dateadd function today minus period

Table.Addcolumn needs a table parameter where you want to add the column. In my case the table is called '"Changed Type" which is the name of my last transformation step before Table.AddColumn.

 

Your approach was just by using the button "custom column" So there you just write 

 

if [EndDate]<Date.AddMonths(Date.From(DateTime.LocalNow()),-3) then 1 else 0)

The rest will be added by Query Editor itself. 

View solution in original post

Highlighted
Resolver I
Resolver I

Re: dateadd function today minus period

still not working

I've altered it to:

 

= if [lastLD]>Date.AddMonths(Date.From(DateTime.LocalNow()),-3) then 1 else 0

The column gets created but only contains Error.

 

If I add an ) on the end, I get a Token Eof expected error, which seems valid to me, since that one doesn't belong to anything.

 

de lastLD is formatted in d-m-yyyy h:mm:ss

Highlighted
Resolver IV
Resolver IV

Re: dateadd function today minus period

So when you click on the word error he will show you some details about it.

 

However

 

You say that your date column is in datetime format? Could you please write Date.From([lastLD]) instead of [lastLD]

 

Brackets are ok as far as i see

View solution in original post

Highlighted
Resolver I
Resolver I

Re: dateadd function today minus period

Superb, it working now.

Thank you very much.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors