topic Re: Column reference to 'Date' in table '...' cannot be used with a variation 'Date' in DAX Commands and Tips

Column reference to 'Date' in table '...' cannot be used with a variation 'Date'

Anonymous — Sat, 28 Mar 2020 22:38:58 GMT

Hello, I am running into an error that occurs when I try to couple two different actions in Power BI. Action 1 - I am creating a new column that finds the corresponding value for the same day the previous month base on the Date column (confirmed Date Type format) and this value is called 'Lease Payment (Cash Basis)'. This is the DAX formula we are using: =CALCULATE( MIN('Table'[Value]), ALLEXCEPT('Table'[Value2]), PREVIOUSMONTH('Table'[Date].[Date]) ) Action 2 - I am using the approach found if you Google “Currency conversion for Power BI and SSAS” by Kasper On BI. I tried to input the URL via the LINK tool and directly as text, but it appears to be causing an issue. Both of these approach work for me separately. But when I create the required relationships for Action 2 as shown below, I get the error message for the DAX formula in Action 1, also shown below:

Re: Column reference to 'Date' in table '...' cannot be used with a variation 'Date'

Anonymous — Sat, 28 Mar 2020 22:40:41 GMT

I am sorry the formatting above is horrible, I am having a nightmare with this website today. This was my 4th attempt at posting this question. Not sure what I'm doing wrong. Trying to upload the screen shots again as those failed to post with the rest of it.

Re: Column reference to 'Date' in table '...' cannot be used with a variation 'Date'

Anonymous — Sat, 28 Mar 2020 23:07:14 GMT

Using the automatically created date hierarchy is a SURE way to screw things up in no time. Please create a proper Calendar.

Also, if you have a big fact table, using CALCULATE in a calculated column will slow things down so much that sometimes it'll not come to a stop.

PREVIOUSMONTH works correctly only on a proper Data table. Never on a column in a fact table.

Best
D

Re: Column reference to 'Date' in table '...' cannot be used with a variation 'Date'

Anonymous — Mon, 30 Mar 2020 17:14:45 GMT

Thanks for bringing the importance of the CALENDAR table to my attention.

I have gone ahead and created this table in my model. Then in the relationship (attached in original message) I put this table between the Lessor/Lessee Data table and the Exchange Data table connected by the date fields. The same error persists, however.

Is this error message one that you would expect to find if the CALCULATE function is causing the custom column to time out/stop?

Thanks,

Alex

Re: Column reference to 'Date' in table '...' cannot be used with a variation 'Date'

Anonymous — Mon, 30 Mar 2020 17:42:11 GMT

ExchangeDate is completely unnecessary. One of the reasons is the 1-1 connection between this table and Calendar.

Best
D

Re: Column reference to 'Date' in table '...' cannot be used with a variation 'Date'

Anonymous — Mon, 30 Mar 2020 18:02:42 GMT

Once you have a Calendar table in your model, you CANNOT use any other date field that's connected to this table. All columns in your fact tables MUST be hidden. Please watch this to learn about good modeling habits:

https://www.youtube.com/watch?v=_quTwyvDfG0

and

https://www.youtube.com/watch?v=78d6mwR8GtA&t=1247s

Here's material on time-intel:

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

Best
D