Date Crossfilter If Statement

[vega]

Hello, 

I have the following formula and it is throwing an error and I am not sure why

VAR test =
IF(
	ISCROSSFILTERED(Dates[Date]), 
	Dates[Date], 
	DATESBETWEEN(Dates[Date], MIN(Dates[Date]), TODAY()-1)
)

Basically, I am trying to say if the date table is filtered, then just return the date table, else give me the dates between the earliest date found and yesterday. I want to then take this variable and use it in some time intelligence functions. When I try to use this formula, I get:

"Single value for column 'Date' in table 'Dates' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

I don't want a single value, I want the variable to contain a date table so that I can use it with time intelligence functions. Can someone shed light on what I am doing wrong?

[Zubair_Muhammad]

Hi @vega

Try this. Could you share the complete MEASURE you are using

VAR test =
IF(
	ISCROSSFILTERED(Dates[Date]), 
	ALL(Dates[Date]), 
	DATESBETWEEN(Dates[Date], MIN(Dates[Date]), TODAY()-1)
)

[vega]

Sure, I didn't want to complicate it with a lot of code.

Current Actual + Forecast Room Revenue:= 
VAR maxdate = MAX(Dates[Date])
VAR R3M =
CALCULATE(
	[Actual + Forecast Room Revenue],
	ALL(Dates[Date]),
	DATESBETWEEN(
		Dates[Date],
		DATE(YEAR(maxdate), MONTH(maxdate)-2, DAY(maxdate)),
		maxdate
	)
)
VAR R12M =
CALCULATE(
	[Actual + Forecast Room Revenue],
	ALL(Dates[Date]),
	DATESBETWEEN(
		Dates[Date],
		DATE(YEAR(maxdate), MONTH(maxdate)-11, DAY(maxdate)),
		maxdate
	)
)
VAR test =
IF(
	ISCROSSFILTERED(Dates[Date]), 
	Dates[Date], 
	DATESBETWEEN(Dates[Date], MIN(Dates[Date]), TODAY()-1)
)
RETURN

SWITCH(
	SELECTEDVALUE(Periods[Period]),
	"YTD", TOTALYTD([Actual + Forecast Room Revenue],test),
	"MTD", TOTALMTD([Actual + Forecast Room Revenue], test),
	"QTD", TOTALQTD([Actual + Forecast Room Revenue], test),
	"R3M", R3M,
	"R12M", R12M,
	[Actual + Forecast Room Revenue]
)

[mattbrice]

First, the error you got is because Dax can't tell which row(s) of Dates[Date] you are referring to. You need to do like suggested:  ALL (Dates[Date] ) or VALUES ( Dates[Date] ).  But even more importantly ...

If you replace 'test' with 'Dates[Date]' , the 3 switch lines 'YTD', 'QTD' and 'MTD' should yield the same results.  The functions TOTALYTD, TOTALQTD and TOTALMTD second paramter is only a date column refernce - It doesn't use the column you computed otherwise.  Put another way, I don't believe the variable 'test' is impacting the calculation like you think it is.

Dax converts your call to the TOTALYTD function to this equivalent in Dax:

TOTALYTD Measure = CALCULATE ( [Actual + Forecast Room Revenue], 
CALCULATETABLE ( FILTER ( ALL ( 'Dates'[Date] ), 
AND ( 'Dates'[Date] <= MAX ( 'Dates'[Date] ), 
      YEAR ( 'Dates'[Date] ) = YEAR ( MAX ( 'Dates'[Date] ) ) ) ) )

TOTALQTD & TOTALMTD Dax are similar.

so with the ALL( ) function call, the column values in your 'test' var are being overwritten. (or more precisely, your 'test' column filters are being blocked & replaced by this setfilter argument to CALCULATE).

It is hard to say what the measure code should be without fully understanding what you are trying to do.  I'm not sure i understand why you want to detect if 'Dates' is being crossfiltered(?) But hope this helps.  

[vega]

I have a question about the code below:

TOTALYTD Measure = CALCULATE ( [Actual + Forecast Room Revenue], 
CALCULATETABLE ( FILTER ( ALL ( 'Dates'[Date] ), 
AND ( 'Dates'[Date] <= MAX ( 'Dates'[Date] ), 
      YEAR ( 'Dates'[Date] ) = YEAR ( MAX ( 'Dates'[Date] ) ) ) ) )

The ALL function there is wiping out the filters from the test variable but it is replacing the filter with a date filter that will contain the year to date of the test variable. This is exactly the expected behavior that I want. This is what I am trying to accomplish here:

My data contains forecast data that can go into 2019. When the user wants a YTD, but they do not specify the dates (is not crossfiltered), then I want to retrieve the YTD from yesterday, hence TODAY()-1. If the user does specify a date, then just calculate the YTD normally, hence just return Dates. If I just use the Date column, then if the user does not specify a date, we get YTD of the last months of data in 2019.

I tried the following:

VAR testdate =
IF(
	ISCROSSFILTERED(Dates[Date]), 
	VALUES(Dates[Date]),
	DATESBETWEEN(Dates[Date], MIN(Dates[Date]), TODAY()-1)
)

But when I use that this I get "The first argument in DATESYTD must specify a column". I'm sure this is because I am feeding testdate to TOTALYTD and because testdate is not a column, I am getting this error. I guess my question is there any way to get DAX to understand that testdate is a column, because technically it is a one column table?

I appreciate the help you guys have given me so far. Thanks!

[mattbrice]

What do want output visual to look like?  What is on rows/columns?  How are users selecting dates?

All the TOTALXXX functions need as a second parameter is a single date. That single date will be used to expand the date range as needed per the function called.

But if you put year, quarters, and months on rows of visual, all three TOTALXXX functions should compute correctly. No need to detect selection.  Slicers could be used to filter visual to subset of data though. 

So so a sample output would be helpful. 

[vega · Accepted solution]

The output visual was supposed to contain no date selections and no dates on the rows and columns. This is why the formula proved to be difficult. I was able to get the formula to work using a slightly different approach. I'll leave the formula here in case anyone in the future faces a similar problem.

Current Actual + Forecast Room Revenue:= 
VAR maxdate = MAX(Dates[Date])
VAR R3M =
CALCULATE(
	[Actual + Forecast Room Revenue],
	ALL(Dates[Date]),
	DATESBETWEEN(
		Dates[Date],
		DATE(YEAR(maxdate), MONTH(maxdate)-2, DAY(maxdate)),
		maxdate
	)
)
VAR R12M =
CALCULATE(
	[Actual + Forecast Room Revenue],
	ALL(Dates[Date]),
	DATESBETWEEN(
		Dates[Date],
		DATE(YEAR(maxdate), MONTH(maxdate)-11, DAY(maxdate)),
		maxdate
	)
)
RETURN

SWITCH(
	SELECTEDVALUE(Periods[Period]),
	"YTD", 
		IF(
			ISCROSSFILTERED(Dates[Date]), 
			TOTALYTD([Actual + Forecast Room Revenue],Dates[Date]), 
			TOTALYTD([Actual + Forecast Room Revenue], DATESBETWEEN(Dates[Date], MIN(Dates[Date]), TODAY()-1))
		),
	"MTD", 
		IF(
			ISCROSSFILTERED(Dates[Date]),
			TOTALMTD([Actual + Forecast Room Revenue], Dates[Date]),
			TOTALMTD([Actual + Forecast Room Revenue], DATESBETWEEN(Dates[Date], MIN(Dates[Date]), TODAY()-1))
		),
	"QTD", 
		IF(
			ISCROSSFILTERED(Dates[Date]), 
			TOTALQTD([Actual + Forecast Room Revenue],Dates[Date]), 
			TOTALQTD([Actual + Forecast Room Revenue], DATESBETWEEN(Dates[Date], MIN(Dates[Date]), TODAY()-1))
		),
	"R3M", R3M,
	"R12M", R12M,
	[Actual + Forecast Room Revenue]
)

As you can see, I just did an if statement that will either give me the time intelligence function for all dates if the dates are filtered or the time intelligence function for yesterday if the dates are not filtered. I realize that this is not a typical use case, it was just a special request from my supervisor.

Thank you all for all of your help, I couldn't have gotten to the solution without you all. Thank you!