Forum Discussion

vega's avatar
vega
Resolver III
8 years ago
Solved

Date Crossfilter If Statement

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?

Need Help
  • vega's avatar
    vega
    8 years ago

    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!

6 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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's avatar
      vega
      Resolver III
      8 years ago

      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's avatar
        mattbrice
        Solution Sage
        8 years ago

        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.