Expressions that yield variant data-type cannot be used to define calculated columns.

[R00k1eM1stake]

Hi, let me just start by saying thank you for any advice you can give me.

I appear to be getting the following error message when I create a Dax expression.  Any suggestions on how to resolve the below error message?

Error: "Expressions that yield variant data-type cannot be used to define calculated columns."

Dax Formula:

"Latest Full 4 Quarters = IF((MONTH(MAX(Headcount[As of Date]))=3)||(MONTH(MAX(Headcount[As of Date]))=6)||(MONTH(MAX(Headcount[As of Date]))=9)||(MONTH(MAX(Headcount[As of Date]))=12),IF(AND(Headcount[As of Date]<=MAX(Headcount[As of Date]),(Headcount[As of Date]>(MAX(Headcount[As of Date])-365))),VALUE(1),(VALUE(0))),IF((MONTH(MAX(Headcount[As of Date]))=1)||(MONTH(MAX(Headcount[As of Date]))=4)||(MONTH(MAX(Headcount[As of Date]))=7)||(MONTH(MAX(Headcount[As of Date]))=10),IF(AND(Headcount[As of Date]<=(MAX(Headcount[As of Date])-28),(Headcount[As of Date]>(MAX(Headcount[As of Date])-393))),VALUE(1),VALUE(0)),IF((MONTH(MAX(Headcount[As of Date]))=2)||(MONTH(MAX(Headcount[As of Date]))=2)||(MONTH(MAX(Headcount[As of Date]))=5)||(MONTH(MAX(Headcount[As of Date]))=8)||(MONTH(MAX(Headcount[As of Date]))=11),IF(AND(Headcount[As of Date]<=(MAX(Headcount[As of Date])-56),(Headcount[As of Date]>(MAX(Headcount[As of Date])-421))),VALUE(1),VALUE(0))=0)))"
 

Feel free to make fun of me for making this ridiculously long formula...a similar version worked for me in excel, but I can't get it to work in DAX.

Our Organization likes to look at data in three time periods:

1. The most recent completed month's data (since today is August 19th, we are looking at July data).

2. The Year-To-Date (this year's data up to the end of the most recent month).

3. A four quarter view (of the last four fully completed quarters).

It's this last view which is challenging me as it requires a rolling 12 month period that sometimes has a lag of either 0, 1, or 2 months.  I apologize if this explanation makes your head spin. To give example, in January of 2019 we looked at the last four quarters data (Q1, Q2, Q3, & Q4 of 2018). We continued to look at these four quarters in February and March of 2019...but in April of 2019 we then had a new completed quarter to look at so the period changed to view Q2, Q3, & Q4 of 2018 and Q1 of 2019. 

Power BI offers some great features for filtering your "as of date" with relative date filtering, but my organization has a few processes which make it difficult to use this feature since we don't necessarily load our data on the first of every month so I cannot use a relative date feature to advance to the next set of data.  What I'm attempting to do is build a column that will indicate each row of data that falls within the last four completed quarters.  I'd use this column as my filter for the page so every month the Dax formula will identify the correct rows to include in the report. 

We are attempting to avoid having to advance the month's manually since the report has a huge number of bookmarks that would require updates each month.  I've built several columns using if formulas.  I was able to create the formulas for "Latest Month", "Latest Year," and three rolling 12 months options to work successfully.  The "Latest Full 4 Quarters" column is where the error occurs.  It is essentially a series of IF statements attempting to have a 1 or 0 for each row depending on if the row is part of the four quarter window we need to look at.  If the month's data is in the correct 4 quarters a 1 will be listed in the row, if not a 0 will be displayed.  Then on the page, we can simply set the filter for 1 for this field and get the desired filtration for the 1 quarters needed without having to manually change an "as of date" range.  Hope that makes sense.

I even attempted to create a simpler version where the column was tied to the three rolling 12 month options (so that if the latest month is Mar, Jun, Sep, or Dec you'd use the standard rolling 12 months, if the latest month is Jan, Apr, Jul, or Oct you'd use the 1 month lag rolling 12 months, and if the latest month is Feb, May, Aug, or Nov you'd use the 2 month lag rolling 12 months. Sadly this got the same error as the convoluted one I've posted above.

I'm hoping I've just overlooked something extremely simple in the values, but for the life of me I cannot figure it out. I've searched other forum postings and can't seem to find a solution that works.

Any advice would be greatly appreciated.

I apologize for any pour choice of words used above as this is my first attempt at a post and I'm very new to the Power BI Community.

I've included links below to a simplified version of the report and data.  I hope this helps.

Quarterly Data Test: Power BI File

Headcount1 Data Source

NewHdcnt dimensions Data Source

[Anonymous]

No idea why you get this error but there MUST be a good reason for this.

But the error really is irrelevant. What you're doing with your monster formulas is not the way to handle this. Instead, you should create a proper Date table in your model first of all. You could connect it to the date field in your fact table or leave it disconnected (and most likely hidden). That depends on some other conditions but 95% of the time you should connect it and leave visible to enable slicing by time. In the date table you should have a column that tells you which quarter a date is in and the quarters should be numbered consecutively with integers (this field will be hidden). So, you should have a column holding strings 2018-Q1, 2018-Q2,... and a column that numbers them 1, 2, 3... Once this is in place, it's easy to tell if a date in your fact table falls into the 4-quarter window you want. Say your max date in the fact table is D and it's in quarter Q. Say you have a date D1. Just check if D1 is in one of the quarters Q-1, Q-2, Q-3, Q-4 (Q is the integer I mention above). I'd suggest to put the field (as well as others of this type - you get the idea) in the Date table, not in the fact table. Then slicing will be easy and the model will be as small as possible, your DAX will be simple. Please note that adding columns to slice by to dimensions is the preferred option. ALWAYS.

Please read about what a proper Date table is in a tabular model and why it's so vital to everything you do. This will save you a lot of frustration and head-scratching down the line. Also, for the time-intel functions to work properly you need a Date table.

Also, please bear in mind that if your DAX becomes complex and hard to understand... it's a sign your model is incorrect.

Best
Darek