Measure used as input of other measure - error

[ph]

Hi all,

I have measure this measure

    last date = LASTDATE('Date'[Date].[Date])

but I want to get last date - 12 month like this 

    last date - 12M = DATEADD(Table[last date]; -12;MONTH)

I received an error that parameter is not in correct type, but when I write manually the code to the measure it works

last date - 12M = DATEADD(LASTDATE('Date'[Date].[Date]); -12;MONTH)

any idea why cannot use 1) option?

thx

[Cmcmahan · Accepted solution]

In order for PowerBI to recognize a custom date table as something more than a table that happens to have dates in it, you have to mark it as a date table.

Going back to the measure vs calculated column, measures will show up in the Fields pane with all your other fields, but they don't actually exist in the table itself.  You can see here that the icon for a measure (a calculator) is different than the icon for a calculated column (table with function symbol).

If [last date] is a measure, then you should be able to get rid of the table reference in your [last date - 12M] measure like so:

 last date - 12M = DATEADD([last date]; -12;MONTH)

Unfortunately, you'll still get the same error.  This is because DATEADD expects a column of date values. You're passing it a single date value from your [last date] measure.  This may seem confusing because according to the documentation, LASTDATE returns a table with a single column and row with the last date from the original list. 

However, when the result of a DAX measure is a single column/single row table with a value, the measure will return just the value, not the entire table.  So when you pass the result of the [last date] measure to DATEADD, you're technically getting a different output than when you use the whole LASTDATE expression.

You could try using EDATE instead of DATEADD, since it accepts a single date instead of a date column. More than likely, I would just do what you did, which is to explicitly write out the entire expression in the second measure.

Funnily enough, I actually found this which uses the exact issue you're facing as an instructional tool for this problem: https://powerpivotpro.com/2018/06/navigating-dax-function-types-a-list-of-dax-scalar-and-table-funct...

[Anonymous]

A piece of good advice: Please stop using automatically generated date tables and create your own. You'll thank me later.

What is Table[last date]? You said "last date" is a measure, not a column, so how can you use the measure as a column name? YOU CANNOT. The manually written works because you are passing a date column from a date table but what you do is very bad and brittle.

Read this as well: www.daxguide.com/dateadd and pay attention to the description of the arguments.

Best
Darek

[ph]

Hello @Anonymous 

thanks for response

1) I creating and using my own tables and not automatically generated. Can you be please more specific what is wrong from your point of view?

2) Yes, it is measure, that is valid for each row (is the same for each row) and when I added to the table I see it as a column (same value for the each row)

I am sorry but this is unlogic, the measure returns always the only one output and you wrote that when is it nested it works..or is it some special feature of DAX?

thanks

[Anonymous]

Mate, if you use such an expression "last date = LASTDATE('Date'[Date].[Date])" it means invariably that you are using the automatically generated date table.

Please stop doing that unless you want to be in big trouble later on.

Secondly, you don't seem to be able to tell the difference between a measure and a calculated column. Would you please learn a bit about it? There's plenty of material on the Web. It's just a matter of willingness to learn.

"I am sorry but this is unlogic, the measure returns always the only one output and you wrote that when is it nested it works..or is it some special feature of DAX?" Please try to read my words with the proper understanding of them.

Thanks.

Best
D.

[ph]

Hello @Anonymous 

1) really don t know how, why is it happen..it must be caused PBI, because I fill dim Date my own values, and when I start writing code the 'hint' finish it...really don t know what s happen and how to fix it

2) Believe me that I am trying hard to understand DAX, contexts..materials are plenty as you wrote but most of them are focusing on the skilled DAX experts and not newbies like me (i.e. MS official documentation, if you want to understand example of mentioned function which they describe, you must learn all mentioned function before you reach the one that is descibe in article, so without basic knowledge you cannot learn new function). The Italians , very similar case..they are focusing on professionals...therefore I am writing my questions here, because I have no contact/chance to ask to any profesional...if you have link on the DAX for dummies with plenty of examples I will be happy..I am sorry if my questions are weird, but I am trying to understand how is it works...

[Anonymous]

Hi there.

Please, start with this book:

Matt Arlington, How to Write DAX (£9.64 for the Kindle edition)

It should be digestible for newbies like you.

Secondly, if you had a proper Date table connected to your fact table, the automatically generated one would also automatically disappear. Since it has not, I have a right to assume your model is not correct and you're still relying on the automatic tables. This is bad for many reasons, too many to tell you about them here. But you can take my word for that.

Best

Darek